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.
Go to Top of Page

itaybarda
Starting Member

12 Posts

Posted - 2008-05-01 : 10:21:59
thanks
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -