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.
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]); If it is MSSQL, then 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 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 a table in joining then we use both tables in from close
