Posted By: Anonymous
I want to update a column in a table making a join on other table e.g.:
UPDATE table1 a INNER JOIN table2 b ON a.commonfield = b.[common field] SET a.CalculatedColumn= b.[Calculated Column] WHERE b.[common field]= a.commonfield AND a.BatchNO = '110'
But it is complaining :
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ‘a’.
What is wrong here?
You don’t quite have SQL Server’s proprietary
UPDATE FROM syntax down. Also not sure why you needed to join on the
CommonField and also filter on it afterward. Try this:
UPDATE t1 SET t1.CalculatedColumn = t2.[Calculated Column] FROM dbo.Table1 AS t1 INNER JOIN dbo.Table2 AS t2 ON t1.CommonField = t2.[Common Field] WHERE t1.BatchNo = '110';
If you’re doing something really silly – like constantly trying to set the value of one column to the aggregate of another column (which violates the principle of avoiding storing redundant data), you can use a CTE (common table expression) – see here and here for more details:
;WITH t2 AS ( SELECT [key], CalculatedColumn = SUM(some_column) FROM dbo.table2 GROUP BY [key] ) UPDATE t1 SET t1.CalculatedColumn = t2.CalculatedColumn FROM dbo.table1 AS t1 INNER JOIN t2 ON t1.[key] = t2.[key];
The reason this is really silly, is that you’re going to have to re-run this entire update every single time any row in
table2 changes. A
SUM is something you can always calculate at runtime and, in doing so, never have to worry that the result is stale.