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

You can update the first table using an `OUTPUT` clause to capture the records being updated. Then use this information for updating the second table. — Gordon Linoff, Jun 18 '15 at 12:11
why not simply write 2 update statements? you can put them inside a transaction if you need all or nothing. — Zohar Peled, Jun 18 '15 at 12:13

Gordon Linoff · Accepted Answer · 2015-06-18T14:23:23.560

8

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

edited Jun 18 '15 at 14:23

answered Jun 18 '15 at 12:20

Gordon Linoff

1,242,037
58
646
786

thx! extacly what i needed. note that the OUTPUTstatment should be before the WHERE statemnet – Dardar Jun 18 '15 at 13:08

score 1 · Answer 2 · edited May 23 '17 at 11:54

Question has been asked before:

How to update two tables in one statement in 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;

score 1 · Answer 3 · answered Jun 18 '15 at 12:20

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

How to update multiple tables with single query

3 Answers3