0 votes
1 view
ago in SQL by (5.6k points)

I want to update two tables in one go. How do I do that in SQL Server 2005?

UPDATE 

  Table1, 

  Table2

SET 

  Table1.LastName='DR. XXXXXX', 

  Table2.WAprrs='start,stop'

FROM 

  Table1 T1, 

  Table2 T2

WHERE 

  T1.id = T2.id

AND 

  T1.id = '010008'

1 Answer

0 votes
ago by (9.5k points)

It's not possible to update multiple tables in one statement, however, you can use the transaction to make sure that two UPDATE statements must be treated atomically. 

You can also batch them to avoid a round trip like this.

BEGIN TRANSACTION;

UPDATE Table1

  SET Table1.LastName = 'DR. XXXXXX' 

FROM Table1 T1, Table2 T2

WHERE T1.id = T2.id

and T1.id = '011008';

UPDATE Table2

SET Table2.WAprrs = 'start,stop'

FROM Table1 T1, Table2 T2

WHERE T1.id = T2.id

and T1.id = '011008';

COMMIT;

...