How to update multiple tables in sql server 2008 ?

Question

Hi All,

I want to update multiple columns from multiple tables in a single UPDATE Query...

Does Sql Server 2008 provide any mechanism to do so?

If Sql Server 2008 provide such nice approach, please share some links with me!

Thanks!

Answer 1

Try this

UPDATE Table1, Table2
SET Table1.Column1 = 'one' 
,Table2.Column2 = 'two'
FROM Table1 T1, Table2 T2
WHERE T1.id = T2.id
and T1.id = 'id1'

Answer 2

Try this

UPDATE Table1, Table2
SET Table1.Column1 = 'one' 
,Table2.Column2 = 'two'
FROM Table1 T1, Table2 T2
WHERE T1.id = T2.id
and T1.id = 'id1'

This is absolutely wrong... don't mislead people ..

You can not update multiple table in one single update statement..  what you can do is wrap the update statement in a transaction, commit changes only when both update are successful

e.g

begin try

begin tran

update table1 set c1=2

update  table2 set c1=2 

commit

end try 

begin catch

rollback

end catch

vt

 

 

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 3

http://stackoverflow.com/questions/13288803/sql-server-update-multiple-rows-dynamically

http://geekswithblogs.net/phoenix/archive/2009/10/13/update-multiple-columns-on-sql-server.aspx

---

Regards, Dineshkumar
Please "Mark as Answer" if my post answers your question and "Vote as Helpful" if it helps you

Answer 4

Thanks,

But its not going to work!

Answer 5

You can't update 2 tables in a Single statement.

http://stackoverflow.com/questions/5252802/how-to-use-update-trigger-to-update-another-table

---

Narsimha

Answer 6

OF COURSE NOT! Think about cascading DRI actions and how a set-oriented language has to work. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL