CODEWITHSUNDEEP
sqlsql-server
Dardar
Dardar

Reputation: 751

How to update multiple tables with single query

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

Upvotes: 7

Views: 56865

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

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

Upvotes: 10

massie
massie

Reputation: 556

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;

Upvotes: 1

Simone
Simone

Reputation: 1924

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

Upvotes: 1

Related Questions