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

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.

Answer 2

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 3

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.

Answer 4

MERGE table1 T
   USING table2 S
      ON T.CommonField = S."Common Field"
         AND T.BatchNo = '110'
WHEN MATCHED THEN
   UPDATE
      SET CalculatedColumn = S."Calculated Column";

Answer 5

Seems like SQL Server 2012 can handle the old update syntax of Teradata too:

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

If I remember correctly, 2008R2 was giving error when I tried similar query.

Answer 6

I find it useful to turn an UPDATE into a SELECT to get the rows I want to update as a test before updating. If I can select the exact rows I want, I can update just those rows I want to update.

DECLARE @expense_report_id AS INT
SET @expense_report_id = 1027

--UPDATE expense_report_detail_distribution
--SET service_bill_id = 9

SELECT *
FROM expense_report_detail_distribution erdd
INNER JOIN expense_report_detail erd
INNER JOIN expense_report er 
    ON er.expense_report_id = erd.expense_report_id 
    ON erdd.expense_report_detail_id = erd.expense_report_detail_id
WHERE er.expense_report_id = @expense_report_id

Answer 7

    UPDATE mytable
         SET myfield = CASE other_field
             WHEN 1 THEN 'value'
             WHEN 2 THEN 'value'
             WHEN 3 THEN 'value'
         END
    From mytable
    Join otherTable on otherTable.id = mytable.id
    Where othertable.somecolumn = '1234'

More alternatives here.

Answer 8

Another approach would be to use MERGE

  ;WITH cteTable1(CalculatedColumn, CommonField)
  AS
  (
    select CalculatedColumn, CommonField from Table1 Where BatchNo = '110'
  )
  MERGE cteTable1 AS target
    USING (select "Calculated Column", "Common Field" FROM dbo.Table2) AS source ("Calculated Column", "Common Field")
    ON (target.CommonField = source."Common Field")
    WHEN MATCHED THEN 
        UPDATE SET target.CalculatedColumn = source."Calculated Column";

-Merge is part of the SQL Standard

-Also I'm pretty sure inner join updates are non deterministic.. Similar question here where the answer talks about that http://ask.sqlservercentral.com/questions/19089/updating-two-tables-using-single-query.html

Answer 9

I had the same issue.. and you don't need to add a physical column.. cuz now you will have to maintain it.. what you can do is add a generic column in the select query:

EX:

select tb1.col1, tb1.col2, tb1.col3 ,
( 
select 'Match' from table2 as tbl2
where tbl1.col1 = tbl2.col1 and tab1.col2 = tbl2.col2
)  
from myTable as tbl1

Answer 10

Aaron's approach above worked perfectly for me. My update statement was slightly different because I needed to join based on two fields concatenated in one table to match a field in another table.

 --update clients table cell field from custom table containing mobile numbers

update clients
set cell = m.Phone
from clients as c
inner join [dbo].[COSStaffMobileNumbers] as m 
on c.Last_Name + c.First_Name = m.Name

Answer 11

I think, this is what you are looking for.

UPDATE
    Table1
SET
    Table1.columeName =T1.columeName * T2.columeName
FROM 
    Table1 T1
    INNER JOIN Table2 T2
        ON T1.columeName = T2.columeName;

Answer 12

Try:

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