How to update Sql Server table column when joining two tables?

Question

I am trying to update a column by joining it to another table. I used the query below, but that gave me an 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 someone help me with this?

Thanks.

Answer 1

On the one hand, you use table aliases that are not defined anywhere ( T2 , T1 , etc.), and this can very well solve your problem. If not, the correct syntax is highly dependent on SQL taste.

For example, in SQL Server syntax

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

Even though you don’t even need a connection here, you just want to

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

In MySQL syntax

 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]); 

Answer 2

If it is MSSQL, the query should be

 UPDATE TABLE_2 SET DEPT='Human Resource' FROM TABLE_1 WHERE TABLE_2.ID = TABLE_1.ID AND TABLE_2.DEPT = 'HR' AND TABLE_1.COMPANY = 1 

Answer 3

UPDATE TABLE_2 SET DEPT='Human Resource' FROM TABLE_1,Table2 WHERE TABLE_2.ID = TABLE_1.ID AND TABLE_2.DEPT = 'HR' AND TABLE_1.COMPANY = 1

Because when we update the table when joining, we use both tables from the closed