How to update multiple tables with single query

Question

I have 2 tables that I need to update:

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

Table B consist of: PersonID, Date, status

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

I need to "loop" over all results from A that the status=2 and update the date and status to 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.

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:

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

thanks for any help

Answer 1

Here is an example using the output clause:

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);

Answer 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