I want to update two tables in one go. How do i do that in SQL Server 2005?
UPDATE Table1, Table2
SET Table1.LastName = 'DR. XXXXXX'
,Table2.WAprrs = 'start,stop'
FROM Table1 T1, Table2 T2
WHERE T1.id = T2.id
and T1.id = '010008'
I want to update two tables in one go. How do i do that in SQL Server 2005?
UPDATE Table1, Table2
SET Table1.LastName = 'DR. XXXXXX'
,Table2.WAprrs = 'start,stop'
FROM Table1 T1, Table2 T2
WHERE T1.id = T2.id
and T1.id = '010008'
Sorry, afaik, you cannot do that. To update attributes in two different tables, you will need to execute two separate statements. But they can be in a batch ( a set of SQL sent to the server in one round trip)
You can't update multiple tables in one statement, however, you can use a transaction two 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 = '010008'
UPDATE Table2
SET Table2.WAprrs = 'start,stop'
FROM Table1 T1, Table2 T2
WHERE T1.id = T2.id
and T1.id = '010008'
COMMIT
You can't update two tables at once, but you can link an update into an insert unsing OUTPUT INTO, and you can use this output as a join for the second update:
DECLARE @ids TABLE (id int);
BEGIN TRANSACTION
UPDATE Table1
SET Table1.LastName = 'DR. XXXXXX'
OUTPUT INSERTED.id INTO @ids
WHERE T1.field = '010008';
UPDATE Table2
SET Table2.WAprrs = 'start,stop'
FROM Table2
JOIN @ids i on i.id = Table2.id;
COMMIT;
I changed your example WHERE condition to be some otther field than id, if is id the you don't need this fancy OUTPUT, you can just UPDATE the second table for the same id='010008'.