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

by

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;

More Related Contents:

  1. Get top 1 row of each group
  2. Insert results of a stored procedure into a temporary table
  3. SET versus SELECT when assigning variables?
  4. How important is the order of columns in indexes?
  5. Using the result of an expression (e.g. Function call) in a stored procedure parameter list?
  6. How to print VARCHAR(MAX) using Print Statement?
  7. Concatenate values based on ID
  8. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  9. For Nvarchar(Max) I am only getting 4000 characters in TSQL?
  10. Splitting delimited values in a SQL column into multiple rows
  11. Is it possible to select sql server data using column ordinal position
  12. varbinary to string on SQL Server
  13. How do I group on continuous ranges
  14. How can a LEFT OUTER JOIN return more records than exist in the left table?
  15. “select * from table” vs “select colA, colB, etc. from table” interesting behaviour in SQL Server 2005
  16. Dynamic SQL to generate column names?
  17. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  18. Column name or number of supplied values does not match table definition
  19. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  20. SQL Server 2005 ROW_NUMBER() without ORDER BY
  21. How to find consecutive rows based on the value of a column?
  22. Can an INNER JOIN offer better performance than EXISTS
  23. How to escape square brackets inside square brackets for field name
  24. How do you copy a record in a SQL table but swap out the unique id of the new row?
  25. Use variable with TOP in select statement in SQL Server without making it dynamic [duplicate]
  26. Compare dates in T-SQL, ignoring the time part
  27. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  28. Is there a quick way to check if ANY column is NULL?
  29. Why do SQL Server Scalar-valued functions get slower?
  30. SQL query for parent-child chain

Leave a Comment