Question: How to Update Two Tables in One Statement?
Answer: Not Possible.
There are some questions where the answer is no and that is totally fine. 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. Well, the question is very valid, but it is truly not possible to do so.
Most of the time when I ask more details about the need of this particular need, the usual answer I get is that the user wants to perform two or more updates together in such a way that when success they both gets committed together or fails together.
There is another way this one feature can be achieved is via transactions.
You can wrap your around your more than one UPDATE statement in a single transaction, this way if one or more statement fails, the entire transaction rolls back.
Here is the sample example of how the transaction should work with multiple update statements.
BEGIN TRANSACTIONS; UPDATE TABLE1 SET Col1 = 'SomeValue' WHERE Cond1 = 'SomeCond'; UPDATE TABLE2 SET Col2 = 'SomeValue' WHERE Cond2 = 'SomeCond'; COMMIT;
Let me know if this solution satisfies the need of updating two tables in a single statement. If not, I would love to know the reason, you want to update two more tables in a single statement.
Reference: Pinal Dave (http://blog.SQLAuthority.com)