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'
You can't update two tables at once, but you can link an update into an insert using OUTPUT INTO, and you can use this output as a join for the second update:
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'.
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.It is as simple as this query shown below.
The short answer to that is no. While you can enter multiple tables in the
from
clause of an update statement, you can only specify a single table after theupdate
keyword. Even if you do write a "updatable" view (which is simply a view that follows certain restrictions), updates like this will fail. Here are the relevant clips from the MSDN documentation (emphasis is mine).UPDATE (Transact-SQL)
CREATE VIEW (Transact-SQL)
In all honesty, though, you should consider using two different SQL statements within a transaction as per LBushkin's example.
UPDATE: My original assertion that you could update multiple tables in an updatable view was wrong. On SQL Server 2005 & 2012, it will generate the following error. I have corrected my answer to reflect this.
You should place two update statements inside a transaction
This works for MySQL and is really just an implicit transaction but it should go something like this:
if you are doing updates to multi tables that require multi statements… which is likely possible if you update one, then another based on other conditions… you should use a transaction.