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

itaybarda
Starting Member

12 Posts

Posted - 2008-05-01 : 10:16:56

this code is not working.
I can’t update 2 tables in one statement?

update pd, p
set pd.show = 0 , pd.seashow = 0
FROM ProductDetail pd
join Products p on p.itemid = pd.ItemID
where pd.show = 1
and pd.site in (1,4,6)

Qualis
Posting Yak Master

145 Posts

Posted - 2008-05-01 : 10:20:17

You cannot update 2 tables or views in one statement.

itaybarda
Starting Member

12 Posts

Posted - 2008-05-01 : 10:21:59

thanks

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-05-02 : 03:27:33

Your pseudo-example doesn't resemble updating two tables, both SET operations only update the product-detail table and it's perfectly legit to have a join-condition in an update statement.

update pd, p
  set pd.show = 0 , pd.seashow = 0
FROM ProductDetail pd
  join Products p on p.itemid = pd.ItemID 
where pd.show = 1 
  and pd.site in (1,4,6)

But as Qualis said, if you really want to update both tables you have to do it in two operations.

--
Lumbago

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources