Update data in one table with data from another table
How to update multiple columns in a table with values from columns in another table.
Table Structures and values:
Create tableemp(Idx int,firstName varchar(100),LastName varchar(100)
,salary int)
Create tableEmpDetail(Idx int,firstName varchar(100),LastName varchar(100),salary int)
Emp Table
idx Firstname Lastname salary
1001 AAAAA YYYYY 10000
1002 BBBBB XXYYY 9000
1003 CCCCC YYXXX 8000
1004 DDDDD YYY 12000
1005 EEEEE YYYY 15000
Empdetail Table
Idx Firstname LastName Salary
1001 JOHN YYYY 20000
1002 AJAY YYYY 7000
1003 SMITH YYXXX 14000
1004 JACOB YYY 11000
1005 ASTLE YYYY 13000
UPDATE Emp SET Firstname = EmpDetail.Firstname,
Lastname = EmpDetail.Lastname,
Salary = EmpDetail.salary
FROM emp , empdetail
WHERE Emp.Idx = EmpDetail.Idx
Results after the update:
Emp Table
idx firstname Lastname salary
1001 JOHN YYYY 20000
1002 AJAY YYYY 7000
1003 SMITH YYXXX 14000
1004 JACOB YYY 11000
1005 ASTLE YYYY 13000
