Update two different tables in a single update query

Question

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90349 More actions · Answer 1

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

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]

Cookies on SQLServerCentral