Posted by Serge Rielau on 01/10/06 08:14

spartacus wrote:
> Hi,
>
> Does SQL support update to multiple rows where values coming from a
> sub-query?
>
> e.g
>
> insert into TABLE1
> select column1, column2, column3 from TABLE2
>
> This is perfectly valid, assumes TABLE1 has only three columns,
> column1, column2, column3.
>
>
> My question: Is there any way to UPDATE values to TABLE1 similarly?
> something like
>
> update TABLE1 set column1= ?, column2= ? , column3= ?
> where .....
> .......... TABLE2
> ..........
>
> OR is it that, sql allows only UPDATEs with one set of values to n
> rows.
>
> Can any one throw some light on this.
>
> -Thanks and Regards,
> Maymon.
>
You can do this two ways:
MERGE INTO T USING S ON T.pk = S.pk
WHEN MATCHED THEN UPDATE SET c1 = S.c1, c2 = S.c2

This is newly added and AFAIK supported only by Oracle 9i (+) and DB2
V8.1 for LUW (+)

In SQL92 (should work in MS SQL Server and any other SQL based RDBMS):

UPDATE T SET (c1, c2) = (SELECT c1, c2 FROM S WHERE pk = T.pk)
WHERE EXISTS(SELECT 1 FROM S WHERE pk = T.pk)

or use an IN predicate: UPDATE ... WHERE pk IN (SELECT pk FROM S)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Back to original message]