如何使用单个查询更新多个表?

I have 2 tables that I need to update:

我有两个表需要更新:

Table A consists of: ID, personName, Date, status

表A包括:ID、person名称、日期、状态

Table B consist of: PersonID, Date, status

表B包括:person、Date、status

For every row in A there can be multiple rows in B with the same personID

对于A中的每一行，在B中有多个行具有相同的person

I need to "loop" over all results from A that the status=2 and update the date and status to 1.

我需要将状态=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.

同样，对于一个状态=2的每一行，我需要更新具有相同personID的B中的所有行。e, a, id = b, person) -我也需要将日期和状态更新为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

感谢任何帮助

3 个解决方案

#1

Here is an example using the output clause:

这里有一个使用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);

#2

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

#3

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.

不能在一条语句中更新多个表，但是，可以使用事务确保以原子方式处理两个更新语句。你也可以批量生产，以避免往返。

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;

#1