update 2 tables in one statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

update 2 tables in one statement

Author

Topic

CrazyT
Yak Posting Veteran

73 Posts

Posted - 2010-04-29 : 13:17:07

using sql 2005

can you update multiple tables in one statement?

update tbl1, tbl2 etc like you can in mysql?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-29 : 13:24:11

I don't think that directly you can update two tables with single update statement.

Workaround is to create a view on the two tables and try updating using it.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

CrazyT
Yak Posting Veteran

73 Posts

Posted - 2010-04-29 : 14:03:46

thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-29 : 14:08:01

Probably not the way you'd want to, but yes you can (with 2008):
http://weblogs.sqlteam.com/peterl/archive/2009/07/29/How-to-insert-into-two-tables-in-one-statement.aspx

Kristen
Test

22859 Posts

Posted - 2010-04-29 : 14:11:37

I presume you mean to actually update TWO tables, rather than update one table including data JOINED from one/many other table(s)?


UPDATE A
SET ColX = B.ColY
FROM TableA AS A
    JOIN TableB AS B
      ON B.SomeID = A.SomeID

CrazyT
Yak Posting Veteran

73 Posts

Posted - 2010-04-29 : 14:32:27

using sql 2005 -- and i was trying to update 2 tables in one statement

Kristen
Test

22859 Posts

Posted - 2010-04-29 : 15:31:27

Then you need to use OUTPUT clause, as per Lamprey's link. But you may think its a bit of a pig-in-a-poke and want to work around that approach (which is what I do

)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources