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...
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
3 Answers
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);
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
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;
Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.
