AppletTalk.com Java discussions newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  standard sql: update multiple rows.          AppletTalk.com Forum Index -> Java and Databases View previous topic :: View next topic   Author Message spartacus Guest Posted: Tue Jan 10, 2006 4:57 am    Post subject: 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. Back to top Serge Rielau Guest Posted: Tue Jan 10, 2006 6:14 am    Post subject: Re: standard sql: update multiple rows. spartacus wrote: Quote: 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 top Erland Sommarskog Guest Posted: Tue Jan 10, 2006 11:27 am    Post subject: Re: standard sql: update multiple rows. Serge Rielau (srielau (AT) ca (DOT) ibm.com) writes: Quote: 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, [email]esquel (AT) sommarskog (DOT) se[/email] Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Back to top spartacus Guest Posted: Tue Jan 10, 2006 11:28 am    Post subject: Re: standard sql: update multiple rows. Thanks serge, It worked. Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         AppletTalk.com Forum Index -> Java and Databases All times are GMT Page 1 of 1   Jump to: Select a forum Java Newsgroups----------------Java DiscussionsWeb-based java games comp.lang.java 3D Graphics API's for Java Java: Support and criticism Java System Announcements comp.lang.java.api JavaBeans Java and CORBA Java and Databases comp.lang.java.developer Java GUI Toolkits Java Help comp.lang.java.javascript JVM, native methods and hardware comp.lang.java.misc Java Language Programming Security and Java comp.lang.java.setup Java Software Tools comp.lang.java.tech ibm.software.java.linux  Non-English Java Newsgroups----------------Java Language (German)Language Java (Français)Java (Italian)  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.