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

Orginal problem from Peter W on the different results from Sybase and Oracle deleted. >Lester Knutsen (lester@access.digex.net) writes: >>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. > Jonathan Leffler ( johnl@informix.com) writes: >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. > John, the results from Informix are correct and satisfactory. The results I would want from this select are all rows from A, and only the rows from B that join with A AND have the required value in B.c2. This is why I used an outer join. Both Informix and Oracle work as expected, it is Sybase that returns an incorrrect and unsatisfactory result. Regards - Lester ############################################################################# # Lester Knutsen lester@access.digex.net # # Advanced DataTools Corporation Voice: 703-256-0267 # # Providing Informix Database Tools and Consulting # #############################################################################