Update table from another table in sql server
Here is the example to update table from another table where Empid is same and according some conditions.
We have to update 'tblEmpdetails' table
'Acno' as mention on second table 'tblEmpAcNo' with the condition that department must be 'Hr', Both table given bellow and updating query also given bellow.
'Acno' as mention on second table 'tblEmpAcNo' with the condition that department must be 'Hr', Both table given bellow and updating query also given bellow.
Table 1 :- tblEmpDetails
| EmpId | age | Dep | Acno |
|---|---|---|---|
| E1 | 20 | IT | 09876565 |
| E2 | 30 | Hr | 75445678 |
| E3 | 31 | Hr | 45674570 |
Table2 :- tblEmpAcNo
| EmpId | Acno |
|---|---|
| E1 | 02537565 |
| E2 | 85245678 |
| E3 | 41546456 |
| E4 | 34074570 |
| E5 | 45379200 |
UPDATE tblEmpDetails SET tblEmpDetails.AcNo = tblEmpAcNo.AcNo
FROM tblEmpAcNo
WHERE tblEmpDetails.EmpId = tblEmpAcNo.EmpId
AND tblEmpDetails.Dept = 'Hr'
GO
