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: <786@philmds.UUCP>
Date: 31 Aug 88 11:33:27 GMT
References: <1398@ektools.UUCP> <1961@tekcae.CAX.TEK.COM> <3104@homxc.UUCP> <769@philmds.UUCP> <391@infmx.UUCP>
Reply-To: leo@philmds.UUCP (Leo de Wit)
Organization: Philips I&E DTS Eindhoven
Lines: 43

In article <391@infmx.UUCP> aland@infmx.UUCP (Dr. Scump) writes:
>In article <769@philmds.UUCP>, leo@philmds.UUCP (Leo de Wit) writes:
   [stuff deleted]... 
>: 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;

The condition that there should be a match slipped my eyes. Sorry for
that. I assumed too hastely that the domain of y.a values was covered
by that of the x.a values. You're correct about that.

About what happens if there's no match, I have to contradict you, at
least Oracle will give me this message:

    ERROR at line xxx: ORA-1426:  single-row subquery returns no rows

and the transaction is rolled back (no updates done).

   [some lines deleted]...

>: 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?

So it's the first time for you now 8-). I'm very sure Oracle uses it
in its documentation.

                       Leo.