SQL 如何使用单个查询更新多个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30914964/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to update multiple tables with single query
提问by Dardar
I have 2 tables that I need to update:
我有 2 个表需要更新：
Table A consists of: ID, personName, Date, status
表A包括：ID、personName、Date、status
Table B consist of: PersonID, Date, status
表 B 包括：PersonID、Date、status
For every row in A there can be multiple rows in B with the same personID
对于 A 中的每一行，B 中可以有多行具有相同的 personID
I need to "loop" over all results from A that the status=2 and update the date and status to 1.
我需要“循环”来自 A 的所有结果，状态 = 2 并将日期和状态更新为 1。
Also, for every row in A that status=2 I need to update all the rows in B that has the same personID (i.e, A.ID==B.PersonID) – I need to update date and status to 1 as well.
此外，对于 A 中 status=2 的每一行，我需要更新 B 中具有相同 personID（即 A.ID==B.PersonID）的所有行——我还需要将日期和状态更新为 1。
So basically, if I was to do this programmatically (or algorithmically) its's something like that:
所以基本上，如果我要以编程方式（或算法方式）执行此操作，则是这样的：
Foreach(var itemA in A)
If (itemA.status = 2)
itemA.status to 1
itemA.date = GetDate()
foreach(var itemB in B)
if(itemB.PersonID == itemA.ID && itemB.status != 2 )
Change itemB.status to 1
Change itemB.date = GetDate()
i know how to update all the rows in B using the following sql statement:
我知道如何使用以下 sql 语句更新 B 中的所有行：
UPDATE
B
SET
status = 1,
date = GETDATE()
FROM
B
INNER JOIN
A
ON
B.PersonID = A.ID
the problem is that i don't know how to also update table A since there can't be multiple tables in an update statement
问题是我不知道如何更新表 A，因为更新语句中不能有多个表
thanks for any help
谢谢你的帮助
回答by Gordon Linoff
Here is an example using the outputclause:
下面是一个使用output子句的例子：
declare @ids table (id int);
update table1
set status = 1
output inserted.id into @ids
where status = 2;
update table2
set status = 1,
date = getdate()
where personid in (select id from @ids);
回答by massie
Question has been asked before:
之前有人问过这个问题：
How to update two tables in one statement in SQL Server 2005?
如何在 SQL Server 2005 中的一条语句中更新两个表？
it is not possible to update multiple tables at once.
一次更新多个表是不可能的。
Summary answer from that question:
该问题的摘要答案：
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.
您不能在一个语句中更新多个表，但是，您可以使用事务来确保以原子方式处理两个 UPDATE 语句。您还可以批量处理它们以避免往返。
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;
For your question something like this would work:
对于你的问题，这样的事情会起作用：
BEGIN TRANSACTION;
UPDATE B
SET status = 1
, date = GETDATE()
WHERE B.PersonId IN ( SELECT ID
FROM A
WHERE A.status = 2
);
UPDATE A
SET status = 1
, date = GETDATE()
WHERE A.status = 2;
COMMIT;
回答by Simone
Put everything inside a transaction and commit if succeeds
将所有内容放入事务中，如果成功则提交
DECLARE @err int
BEGIN TRANSACTION
UPDATE B
SET status = 1, date = GETDATE()
FROM B INNER JOIN A ON B.PersonID = A.ID
WHERE A.status = 2
SET @err = @@ERROR
IF @err = 0
BEGIN
UPDATE A
SET status = 1,
date = GETDATE()
WHERE status = 2
SET @err = @@ERROR
END
IF @err = 0
COMMIT
ELSE ROLLBACK
