Update a table using JOIN in SQL Server?

user_125512 0
Viewed 1053100

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?

sql-update join tsql sql-server
13 Answers

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

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
user_42684440

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
user_102205060

Those who are using MYSQL

UPDATE table1 INNER JOIN table2 ON table2.id = table1.id SET table1.status = 0 WHERE table1.column = 20
user_75760430
Related