Re: UPDATE with correlated subquerys - What should this do?
>
> 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 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.
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.
#############################################################################
# Lester Knutsen lester@access.digex.net #
# Advanced DataTools Corporation Voice: 703-256-0267 #
# Providing Informix Database Tools and Consulting #
#############################################################################