Updating multiple tables with inner join

问题:

I understand that you can select multiple columns from multiple tables by using joins. Is it possible to update multiple columns in multiple tables using joins?

回答1:

Nope.

You can only do an UPDATE or INSERT into one table at a time.

If you need to do multiples, you can enclose them in a transaction to make sure they all pass or fail together, though:

BEGIN TRY
BEGIN TRAN

UPDATE Table1
SET Col1=Value1

UPDATE Table2
SET Col2=Value2

COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT>0 ROLLBACK
<error message reporting here>
END CATCH

回答2:

Not possible, unless you use triggers on the underlying table