standard sql: update multiple rows.

[spartacus]

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.

[Serge Rielau]

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

[Erland Sommarskog]

Serge Rielau (sr*****@ca.ibm.com) writes:

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)

This does not work in SQL Server. The syntax is:

UPDATE T
SET c1 = S.c1,
c2 = S.c2
FROM T
JOIN S ON T.pk = S.pk

The syntax is not very portable (works on Sybase too), but certainly
simple to grasp.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[spartacus]

Thanks serge,
It worked.