Updating multiple tables in sql

Is it really worth doing all that to avoid writing two statements?I have 2 tables that I need to update: Table A consists of: ID, person Name, Date, status Table B consist of: Person ID, Date, status For every row in A there can be multiple rows in B with the same person ID I need to "loop" over all results from A that the status=2 and update the date and status to 1.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) keyword.Summary answer from that 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. WAprrs = 'start,stop' FROM Table1 T1, Table2 T2 WHERE T1= T2and T1= '011008'; COMMIT; @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.you'd have to go UPDATE Table1 , Table2 SET Table1. XXXXXX' WHERE T1= T2DECLARE @ids TABLE (id int); BEGIN TRANSACTION UPDATE Table1 SET Table1. XXXXXX' OUTPUT INTO @ids WHERE T1.field = '010008'; UPDATE Table2 SET Table2.

In this statement, other columns are also updated with respective new values.

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).

Tweet Sometimes, when searching for an answer, we end up making things too much complicated, while easy solutions are just round the corner.

This is the case of a simple task like updating two related tables with just one SQL query. The first contains user names, and the second email addresses related to the first table names.


Leave a Reply