Updating multiple tables sql server
When asked the reason was he had no idea how to use multiple tables with the help of the JOIN clause in the UPDATE statement. Additionally, we want to update the values of Col2 and Col3 only.
When you look at this it looks very simple but when we try to think the solution, I have seen developers coming up with many different solutions for example sometime they write cursor, table variables, local variables etc. Col2)) UPDATE CTE SET Col1 = _Col1, Col2 = _Col2from stackoverflow.“UPDATE Table1 SET Col2 = t2.
Here is the sample example of how the transaction should work with multiple update statements.
Let me know if this solution satisfies the need of updating two tables in a single statement.
You can additionally use MERGE statement to do the same as well, however I personally prefer this method.
Let us clean up the clause by dropping the tables which we have created.performance wise I liked; (not my! guess you already covered it somewhere..; WITH CTE AS (SELECT T1.
I often get asked that as it is possible to select two or more tables in a single SELECT statement, is it possible to UPDATE more than one table in a single table.Wrap the 70 updates within a single transaction and commit/rollback all together when the batch is complete. Wrap the 70 updates within a single transaction and commit/rollback all together when the batch is complete.Based the question's comment chain and a bit of speculation, here's a rough outline: create procedure Update SSN @old SSN char(9), @new SSN char(9) AS begin tran begin try update table1 set SSN = @new SSN where SSN = @old SSN update table2 set SSN = @new SSN where SSN = @old SSN ... update table68 set SSN = @new SSN where SSN = @old SSN commit tran end try begin catch rollback end catch How do I validate a variable against multple database tables How To Build Dynamic Query Based On Multi Value Parameter?However, the easiest and the most clean way is to use JOIN clause in the UPDATE statement and use multiple tables in the UPDATE statement and do the task. As you can see that using JOIN clause in UPDATE statement it makes it very easy to update data in one table from another table. Col2 AS _Col2 FROM T1 JOIN T2 ON T1= T2/*Where clause added to exclude rows that are the same in both tables Handles NULL values correctly*/ WHERE EXISTS(SELECT T1.
Search for updating multiple tables sql server:
Specifies the temporary named result set or view, also known as common table expression (CTE), defined within the scope of the UPDATE statement.