Denis Gobo tries different methods of updating two tables in one statement:
Every now and then you will get someone asking how to update two tables with one statement in SQL Server. The answer is usually, no that is not possible… the person then walks away muttering something about how MySQL allows it.
So I decided to try to see if I could update two tables with one statement. I decided to try a couple of different things
-
view
-
common table expression
-
indexed view
-
instead of trigger
The results are generally negative. I get the benefit of updating multiple tables in a single query, particularly when you have fairly complex logic around determining which records to update, but if you just wrap things in a transaction, at least you get the benefits of atomicity. And if you often have to update a bunch of different tables at the same time, there might be an issue with your data model.