如何在 SQL Server 2005 的一个语句中更新两个表?

2022-01-31 00:00:00 sql sql-server-2005 tsql sql-server

我想一次更新两个表.我如何在 SQL Server 2005 中做到这一点?

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'

推荐答案

你不能在一个语句中更新多个表，但是你可以使用一个事务来确保两个 UPDATE 语句是原子处理.您还可以对它们进行批处理以避免往返.

You can't update multiple tables in one statement, however, you can use a transaction to make sure that two UPDATE statements are treated atomically. You can also batch them to avoid a round trip.

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;

相关文章