leo@philmds.UUCP (Leo de Wit) (08/27/88)
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.
aland@infmx.UUCP (Dr. Scump) (08/30/88)
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"
leo@philmds.UUCP (Leo de Wit) (08/31/88)
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.