UPDATE with correlated subquerys [SUMMARY]

Many thanks to all who responded.

The results of the question what does your RDBMS do for this type of update

UPDATE 	table
SET 	col1 = <correlated subquery>,
        	col2 = <correlated subquery>

1. Accepts the update and gives the intuitive response: Informix, Oracle, DECRdb.
2. Defines the update as syntactically incorrect: Ingres.
3. Accepts the update but gives a non-intuitive response: Sybase.

NOTE: 	The "intuitive" response here is to update all rows of the table setting
	col1 and col2 to whatever the subquery returns..

In terms of SQL standards it appears that this type of update is NOT defined in SQL89
(hence Ingres's behaviour) but is defined in SQL92 as the "intuitive" result.

Nobody has responded with a use for the Sybase version of the update which only updates a subset of the table depending on the subquery results. (Anyone from Sybase care to comment?)

Pete Walker,
Salomon Brothers International.
(peterw_at_data1.sbi.com)
Received on Fri Sep 10 1993 - 10:50:37 CEST