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'
views:
211answers:
4
+1
A:
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)
Charles Bretana
2010-01-11 19:55:23
+7
A:
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
LBushkin
2010-01-11 19:56:34
Actually, I am updating records of these two tables from another temptable. temptable has link to table1 but not table2. How can i update the same record of Table2? How will i link it?
Novice
2010-01-11 20:01:45
This is want i wanna doUPDATE Table1, Table2 SET Table1.LastName = 'DR. XXXX' ,Table2.WAprrs = 'start,stop' FROM table1 T1, ##TempTable tt, Table2 T2 WHERE T1.id = T2.id and T1.phy_code = tt.phy_code
Novice
2010-01-11 20:04:30
Please add this information to your question. It is impossible to read in an unformatted comment.
Aaron Bertrand
2010-01-11 20:35:56
@unknown: Based on your comment, you would need to join across both Table1 and Table2 when you update Table2 if your update query needs the keys from a third table. Regardless of that, you still need to do two separate updates.
LBushkin
2010-01-11 20:52:49
A:
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'.
Remus Rusanu
2010-01-11 21:46:35