How to update two tables in one statement in SQL Server 2005?

Question

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'

Answer 1

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;

You can learn in-depth about SQL statements, queries and become proficient in SQL queries by enrolling in our industry-recognized SQL course.