Update Multiple Tables with one Query

[watoth]

I am trying to write a query to update multiple tables. Here it is:

DECLARE @old_team_id AS int;
DECLARE @new_team_id AS int;

UPDATE Players 
SET team_id = @ new_team_id WHERE team_id = old_team_id
UPDATE managers
SET team_id = @ new_team_id WHERE team_id = old_team_id
UPDATE draft 
SET owner_id = @newteam_id WHERE owner_id = @old_team_id AND year > year(getdate())

Im pretty green when it comes to SQL so if im way off please tell me the best way to do this.

Thank you in advance.

-Bill

[ChrisHunter]

you could possibly update the Players and Managers table in the same update. The Draft table may need to be kept in it's own update because it has the year in it's where clause.

Try something like:

DECLARE @old_team_id AS int;
DECLARE @new_team_id AS int;

UPDATE Players 
SET Players.team_id = @ new_team_id, Managers.team_id = @ new_team_id WHERE team_id = @old_team_id;
UPDATE draft 
SET owner_id = @newteam_id WHERE owner_id = @old_team_id AND year > year(getdate());

[adam_k]

@ChrisHunter: You can't update more than 1 tables with the same query. You can join tables if you want to limit your results, but you can update only the table after UPDATE

@OP: Your way of doing it is the correct one.

[JorgeM]

You may want to also include error checking to make sure that the three tables where updated, otherwise if you encounter an error on one of the updates, your backend data will not be complete.

[BitBlt]

To expand on @JorgeM's idea, you might also consider wrapping all the updates inside a transaction to ensure that you can rollback if there are problems with one of the updates.