UPDATE with correlated subquerys - What should this do?

From: Peter Walker <peterw_at_data1.sbi.com>
Date: 2 Sep 93 10:00:53 GMT
Message-ID: <2263_at_pivot-sts.sbi.com>

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).

SYBASE VERSION. use tempdb
go
create table jem1(
a int,
c int,
d int)
go
create table jema(
a int,
b char(1),
c int)
go

insert into jem1 values (1, 0, 0)
insert into jem1 values (2, 0, 0)
insert into jem1 values (3, 0, 0)
insert into jem1 values (4, 0, 0)
insert into jem1 values (5, 0, 0)

go

insert into jema values(1,"I",99)
insert into jema values(1,"J",98)
insert into jema values(2,"I",89)
insert into jema values(3,"J",79)

go
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
go

On Sybase this results in the following jem1 -

           a c d ------ ------ ------

           2           0           0 
           3           0           0 
           4           0           0 
           5           0           0 
           1          99          98 

It looks like only updates which affect both columns are carried out even though there is no WHERE clause on the update itself. If you specify an outer join in the subquery then all rows are updated as expected.

on Oracle -

	 A	    C	       D
     ----------      ----------     ----------
	 1	   99	      98
	 2	   89
	 3		      79
	 4
	 5

This is what I would intuitively expect.

Is there anyone out there with a system that relies upon the Sybase style of behaviour, I can't think of a use for it myself?

Please reply by email and I will summarize on the net.

Thanks in advance,

Pete Walker,

Salomon Brothers International.
(peterw_at_data1.sbi.com) Received on Thu Sep 02 1993 - 12:00:53 CEST