How to update a Sql Server table column while join two tables?

join, sql-server

Solution

For one thing, you're using table aliases that aren't defined anywhere (`T2`, `T1` etc) and that may very well solve your problem. If not, the correct syntax very much depends on SQL flavor.

For example, in SQL Server the syntax is

UPDATE T2
SET T2.dept = 'HUMAN RESOURCE'
FROM Table2 T2
   INNER JOIN Table1 T1
        ON T1.[ID] = T2.[ID]

Although you don't even need a join here really, you just want

UPDATE Table2 T2
SET T2.dept = 'HUMAN RESOURCE'
WHERE EXISTS(SELECT * FROM  Table1 T1
        ON T1.[ID] = T2.[ID])

In MySQL the syntax is

UPDATE FROM TABLE2 AS T2 
    INNER JOIN TABLE1 as T1 
        ON T2.id = T1.id 
SET T2.Dept = 'Human Resources'

Of Course, the `WHERE EXISTS` approach also works for MySQL

UPDATE FROM Table2 AS T2
   SET Dept="Human Resources" 
   WHERE EXISTS (SELECT * FROM  Table1 T1
            ON T1.[ID] = T2.[ID]);

Problem

I'm trying to update a column while joining it to another table. I've used below query, but it gave me error. ``` UPDATE TABLE_2 INNER JOIN TABLE_1 ON (T2.ID=T1.ID) SET TABLE_2.DEPT='HUMAN RESOURCE' WHERE TABLE_2.DEPT='HR' AND T1.COMPANY =1 ``` Can anyone help me on this? Thanks.

Original source