Update a table using JOIN in SQL Server?

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.

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 given above by Aaron is perfect:

UPDATE a  SET a.CalculatedColumn = b.[Calculated Column]  FROM Table1 AS a  INNER JOIN Table2 AS b  ON a.CommonField = b.[Common Field]  WHERE a.BatchNo = '110';

Just want to add why this problem occurs in SQL Server when we try to use alias of a table while updating that table, below mention syntax will always give error:

update tableName t set t.name = 'books new' where t.id = 1

case can be any if you are updating a single table or updating while using join.

Although above query will work fine in PL/SQL but not in SQL Server.

Correct way to update a table while using table alias in SQL Server is:

update t set t.name = 'books new' from tableName t where t.id = 1

Hope it will help everybody why error came here.