standard sql: update multiple rows.This is a discussion on standard sql: update multiple rows. within the JDBC JAVA forums in Framework and Interface Programming category; 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.... | ||||||||
| | ||||||||
| ||||||||
| Register | FAQ | Calendar | Search | Today's Posts | Mark Forums Read |
|
#1
01-09-2006, 11:57 PM
| |||
| |||
 standard sql: update multiple rows. 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.  |
|
#2
01-10-2006, 01:14 AM
| |||
| |||
| Re: standard sql: update multiple rows. 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 |
|
#3
01-10-2006, 06:27 AM
| |||
| |||
| Re: standard sql: update multiple rows. Serge Rielau (srielau@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, esquel@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 |
|
#4
01-10-2006, 06:28 AM
| |||
| |||
| Re: standard sql: update multiple rows. Thanks serge, It worked. |
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
All times are GMT -5. The time now is 07:30 PM.
