Path: utzoo!attcan!uunet!mcvax!hp4nl!philmds!leo
From: leo@philmds.UUCP (Leo de Wit)
Newsgroups: comp.databases
Subject: Re: Need "PASTE" functionality in Informix
Keywords: Informix, SQL
Message-ID: <769@philmds.UUCP>
Date: 27 Aug 88 07:03:05 GMT
References: <1398@ektools.UUCP> <1961@tekcae.CAX.TEK.COM> <3104@homxc.UUCP>
Reply-To: leo@philmds.UUCP (Leo de Wit)
Organization: Philips I&E DTS Eindhoven
Lines: 32

In article <3104@homxc.UUCP> rvp@homxc.UUCP (R.PHILLIPS) writes:
|I am in need of a "paste"-like function using Informix SQL.
|
|We have tables X and Y, both with columns a and b, declared the same
|in both tables.
|Column a is a unique index, and column b is some data. 
|I want to update each record in table X that has a match (based on column
|a) in table Y, such that the datum in the b column of X receives the value
|of the b column of Y.  
|Y records that do not match any in table X should be ignored, of course.
|
|I now achieve this by what I perceive as a cumbersome sequence of
|SQL statements.
|It seeems there should be more direct approach using SQL, but my
|experimentation has failed to reveal any non-cumbersome techniques.
|
|Any ideas?

My idea (I use Oracle SQL, hope it doesn't make too big a difference):

update X
set b = 
        (select b
         from Y
         where Y.a = X.a);

This is what you would call a synchronized update; the X.a in the
subquery equals the 'current tuple' in the update.

Now let's see if it also works in Informix ... good luck!

             Leo.