How Can I Update Multiple SQL Server Tables in a Single Transaction?

Simultaneous Updates Across Multiple SQL Server Tables

Directly updating multiple tables within a single SQL statement isn't supported in SQL Server 2005. However, you can achieve atomic updates across multiple tables by wrapping your UPDATE statements within a transaction.

Let's illustrate this with an example. Suppose we need to update LastName in Table1 and WAprrs in Table2 where the id column matches a specific value. This can be accomplished using the following transaction block:

<code class="language-sql">BEGIN TRANSACTION;

UPDATE Table1
SET LastName = 'DR. XXXXXX'
FROM Table1 T1 INNER JOIN Table2 T2 ON T1.id = T2.id
WHERE T1.id = '011008';

UPDATE Table2
SET WAprrs = 'start,stop'
FROM Table1 T1 INNER JOIN Table2 T2 ON T1.id = T2.id
WHERE T1.id = '011008';

COMMIT TRANSACTION;</code>

It's crucial to remember that the COMMIT TRANSACTION statement is essential. This ensures that both UPDATE statements either complete successfully together, or both fail, maintaining data integrity and preventing inconsistencies that could arise if only one update were successful. Using explicit JOIN syntax is also preferred for clarity and efficiency over implicit joins.

The above is the detailed content of How Can I Update Multiple SQL Server Tables in a Single Transaction?. For more information, please follow other related articles on the PHP Chinese website!