tsql- update a table using join

Question

I want to update a column in a table making a join on other table e.g.:

begin tran
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?

Any help?

Regards

Manjot

Answer 1

Try:

UPDATE table1 SET CalculatedColumn = ( SELECT [Calculated Column] FROM table2 WHERE table1.commonfield = [common field]) WHERE BatchNO = '110'

Answer 2

Try it like this:

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

(edit: darn typos!)

Answer 3

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 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';

Answer 4

I used dynamic SQL

:-) Thanks