November 26, 2008 at 2:53 pm
I have two tables in SQL Server 2005, T1 and T2.
Need to update both the tables in a single query....
we can go for a statement like below for MySQL, but it doesnt work in SQL Server.
"update T1, T2 set T1.col1 = 5, T2. col1=6"
whats the way to handle this issue in SQL Server....please help in detail.
Thanks
Itzsam
November 26, 2008 at 3:57 pm
An update statement in SQL Server can only update a single table. If you need both tables updated as a unit, then you need to use a transaction.
You can also use TRY/CATCH to contain the update statements and rollback the transaction if either update attempt fails. For example:
Begin Transaction;
Begin Try;
Update t1
Set t1.col1 = 5;
Update t2
Set t2.col1 = 7;
Commit Transaction;
End Try;
Begin Catch;
/* put code in to capture errors here */
Rollback Transaction;
End Catch;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 26, 2008 at 7:25 pm
Why do you need to do this in a UPDATE single statement?
Why can't you use a stored procedure or multiple statements in a transaction?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply