Re: UPDATE with correlated subquerys - What should this do?

Lester Knutsen (lester@access.digex.net) writes: >> We have a problem with UPDATEs and correlated subqueries. >> First the general question. >> Is the SQL - >> UPDATE table >> SET col1 = <correlated subquery>, >> col2 = <correlated subquery> >> defined in the SQL89 standard. If there is any reference to it is it >> valid SQL or is it explicitly disallowed? Can someone with a copy of >> the standard email me the relevant sections. >> We have tried this on Sybase v4.9.1 and Oracle v6 with different results. >> What do you think the effect of the following SQL should be? What is it >> on your system? (Specifically anyone out there with access to Informix >> or Ingres). >I tried this with Informix and got the sames results as you got with >Oracle. I changed your SQL from: >> update jem1 >> set c = (select j2.c from jema j2 where j2.a = j1.a and j2.b="I"), >> d = (select j2.c from jema j2 where j2.a = j1.a and j2.b="J") >> from jem1 j1 >To - Informix SQL used: >update jem1 >set c = (select j2.c from jema j2 where j2.a = jem1.a and j2.b="I"), > d = (select j2.c from jema j2 where j2.a = jem1.a and j2.b="J"); I'm not sure how standard this is, but you might find it preferable to write that sub-select once. Then you can consider changing the second column to j2.d instead of j2.c which might or might not be a mistake. UPDATE jem1 SET (c, d) = ((SELECT j2.c, j2.c FROM jema j2 WHERE j2.a = jem1.a AND j2.b="I")) >I think the different results may be related to the way Sybase handles >outer joins. Its different from Informix and Oracle and I have >had fun benchmarking Sysbase because of this. In Informix when you do: >select * from A, outer B >where A.c1 = B.c1 >and B.c2 =1 >Informix returns ALL rows form A. If a row in B.c2 =1 then the row >from B will be returned otherwise a null will be returned from B. If >there are a 100 rows in table A Informix will return 100 rows. While I agree that the Informix behaviour is less than satisfactory, there is little point in making the outer join if the only rows which will be selected are those with a non-null value in the outer-joined table. The only reason for doing the outer join is because you want rows back where the value in B.c2 could be null, but your condition excludes that. However, I also think that the Informix query pair: SELECT A.c1, B.c2, A.c3, B.c1 c4 FROM A, OUTER B WHERE A.c1 = B.c1 INTO TEMP T; SELECT * FROM T WHERE c2 = 1; should return the same results as the original query. Under Informix, the original query returns a different set of results from the temp table form. >The last time I tried this with Sybase, it returned Only the rows from A >that join with B AND where B.c2 =1. If there are 5 rows in B with c2 = 1 >Sybase will only return 5 rows. Sybase did not do an outer join. Sybase probably did the outer join, but it then correctly eliminated those rows for which B.c2 was null because they didn't satisfy the search criterion B.c2 = 1. If it was really clever, it would have worked out that the OUTER join was irrelevant and converted it to an inner join to produce the same results. Its results meet my criterion: the original form of the query returns the same results as the temp table form. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>