According to the syntax below, UPDATE accepts only one table. And in the FROM clause you can use more than one table. One recommendation for these cases is to hold 2 separate UPDATES and wrap the 2 in a TRANSACTON , using BEGIN TRANSACTION and COMMIT TRANSACTION . In addition to an error handling with ROLLBACK TRANSACTION , if an error occurs in the second UPDATE the first one is undone. Follow the example below, after the syntax.
Syntax
UPDATE
[ TOP ( expression ) [ PERCENT ] ]
{ { table_alias | <object> | rowset_function_limited
[ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
}
| @table_variable
}
SET
{ column_name = { expression | DEFAULT | NULL }
| { udt_column_name.{ { property_name = expression
| field_name = expression }
| method_name ( argument [ ,...n ] )
}
}
| column_name { .WRITE ( expression , @Offset , @Length ) }
| @variable = expression
| @variable = column = expression
| column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression
| @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression
| @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression
} [ ,...n ]
[ <OUTPUT Clause> ]
[ FROM { <table_source> } [ ,...n ] ]
[ WHERE { <search_condition>
| { [ CURRENT OF
{ { [ GLOBAL ] cursor_name }
| cursor_variable_name
}
]
}
}
]
Example
BEGIN TRANSACTION;
BEGIN TRY
//Atualizacao da tabela1 com dados da tabela3 com JOIN
UPDATE dbo.Table1
SET dbo.Table1.ColA = dbo.Table3.ColA + dbo.Table3.ColB
FROM dbo.Table3
INNER JOIN dbo.Table1
ON (dbo.Table3.ColA = dbo.Table3.ColA);
//Atualizacao da tabela2 com dados da tabela1 com JOIN
UPDATE dbo.Table2
SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB
FROM dbo.Table2
INNER JOIN dbo.Table1
ON (dbo.Table2.ColA = dbo.Table1.ColA);
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO
