Path: utzoo!utgpu!water!watmath!clyde!att!ucbvax!hplabs!pyramid!infmx!aland
From: aland@infmx.UUCP (Dr. Scump)
Newsgroups: comp.databases
Subject: Re: Need "PASTE" functionality in Informix
Summary: let's be careful out there...
Keywords: Informix, SQL
Message-ID: <391@infmx.UUCP>
Date: 30 Aug 88 06:26:03 GMT
References: <1398@ektools.UUCP> <1961@tekcae.CAX.TEK.COM> <3104@homxc.UUCP> <769@philmds.UUCP>
Organization: Informix Software Inc., Menlo Park, CA.
Lines: 49

In article <769@philmds.UUCP>, leo@philmds.UUCP (Leo de Wit) writes:
: 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.
: 
: 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);

CAREFUL!  This update statement will update *every* row in table
X, not just those for which there is a match in table Y, if I
read it correctly.  I would expect that any rows in X which do not
correlate (e.g. for which there is no "matching" row in Y) would 
have the column b then updated to NULL.  The original poster wanted
non-correlated rows in X to be left alone (see above).  To do this,
you need a WHERE clause which correlates rows in X to rows in Y, e.g.
       where y.a = x.a;
Note that this WHERE clause differs from that in the subquery; this one
applies to the UPDATE X statement, while the other just qualifies the
subquery in the SET clause to get the proper update value for b.

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

Never heard this term used; I have always heard it as "update with
correlated subquery".  Anybody wanna ask Codd what he calls it?

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

Sure 'nuff; see my previous posting...

:              Leo.

-- 
 Alan S. Denney  |  Informix Software, Inc.  |  {pyramid|uunet}!infmx!aland
 Disclaimer: These opinions are mine alone.  If I am caught or killed,
             the secretary will disavow any knowledge of my actions.
 Santos' 4th Law: "Anything worth fighting for is worth fighting *dirty* for"