Update a table using JOIN in SQL Server?

Question

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?

Answer 1

Try it like this:

    UPDATE a      SET a.CalculatedColumn= b.[Calculated Column]     FROM table1 a INNER JOIN table2 b ON a.commonfield = b.[common field]      WHERE a.BatchNO = '110' 

Answer 2

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 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 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.