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
common table expression
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.