How to update two tables in one statement in SQL Server 2005?

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;

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:

DECLARE @ids TABLE (id int);
BEGIN TRANSACTION

UPDATE Table1 
SET Table1.LastName = 'DR. XXXXXX'  
OUTPUT INSERTED.id INTO @ids
WHERE Table1.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 other field than id. If it's id the you don't need this fancy OUTPUT, you can just UPDATE the second table for the same 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)

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 the update 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)

The view referenced by table_or_view_name must be updatable and reference exactly one base table in the FROM clause of the view. For more information about updatable views, see CREATE VIEW (Transact-SQL).

CREATE VIEW (Transact-SQL)

You can modify the data of an underlying base table through a view, as long as the following conditions are true:

• Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
• The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following:
  • An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.
  • A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable.
• The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.
• TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.

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.

Msg 4405, Level 16, State 1, Line 1

View or function 'updatable_view' is not updatable because the modification affects multiple base tables.

This works for MySQL and is really just an implicit transaction but it should go something like this:

UPDATE Table1 t1, Table2 t2 SET 
t2.field = t2.field+2,
t1.field = t1.field+2

WHERE t1.id = t2.foreign_id and t2.id = '123414'

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. 

You should place two update statements inside a transaction

You can write update statement for one table and then a trigger on first table update, which update second table