Update table according to the another table value

Question

I have two tables Employees

EmpID | EmpName | EmpDob

and WareHouseEmployeers

WarehouseEmpID | position | province

I need to update the Employee table according to the WareHouseEmployers table's values. How do I update the details about the employee table according to warehouse province and position?

I have tried this but it's not working:

UPDATE Employee
SET a.EmpName = 'Steven', a.EmpDob = '5-5-1990'
FROM Employee a, WareHouseEmployee b,
WHERE 
    a.EmpID = b.WareHouseEmpID 
    AND position = 'manager', province = 'central'

Can someone please help me to do that in SQL Server?

You need a join in your update - http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql — Liesel, Jan 21 '16 at 05:22
U can use inner join to update the table Please check my answer — Sanu Antony, Jan 21 '16 at 05:22
Please check your table name `Employees`, In the given query the table name is not correct. — Arulkumar, Jan 21 '16 at 05:51

score 1 · Accepted Answer · edited Jan 21 '16 at 09:54

1

Please use this script

UPDATE a
SET a.EmpName = 'Steven' , a.EmpDob='5-5-1990'
FROM Employee a 
INNER JOIN WareHouseEmployee b ON a.EmpID = b.WareHouseEmpID 
                               AND position = 'manager' 
                               AND province = 'central'

edited Jan 21 '16 at 09:54

TT.

15,774
6
47
88

answered Jan 21 '16 at 05:20

Sanu Antony

364
4
15

this also give erro: `a.EmpName and a.EmpDob` could not be found – SteavenJohns Jan 21 '16 at 05:35
its typo. Set Empployee to Employees – Ashish Rajput Jan 21 '16 at 05:54
A good read about "UPDATE FROM" "I guess that many people using UPDATE … FROM on a daily basis do so without being aware that they are violating all SQL standards." LINK http://sqlblog.com/blogs/hugo_kornelis/archive/2008/03/10/lets-deprecate-update-from.aspx – Neeraj Prasad Sharma Jan 21 '16 at 06:53
@NeerajPrasadSharma I for one do not agree with that article. And **every** major RDBMS violates SQL Standards in some way or form... – TT. Jan 21 '16 at 09:58

Update table according to the another table value

1 Answers1