SQL:Use Join with Update Command

In this blog I will show how to use Join with Update command in SQL.

I had a situation where I had to update the column of a table with the values from other table for all the employees simultaneously.

There were 2 tables.

a) Emp_Info  table which contains a record of all employees like EmpID, EmpName, EmpAddress,EmpAge,TotalSalary.
b) EmpSalary table which contains the salary for each month like SalaryID, EmpID, Month, Salary.

Create table Emp_Info
(
  EmpID INT IDENTITY(1,1),
  EmpName VARCHAR(100), 
  EmpAddress VARCHAR(200),
  EmpAge INT,
  TotalSalary DECIMAL
 )
 Insert Into Emp_Info VALUES('Sumit','B 300 Delhi',25,0)
 Insert Into Emp_Info VALUES('Ajit','N 200 Delhi',25,0)
 Insert Into Emp_Info VALUES('Rajesh','A 100 Delhi',28,0)
 Insert Into Emp_Info VALUES('Vij','F 200 Delhi',30,0)
 Select * from Emp_Info

Create table Emp_Salary
(
 SalaryID INT IDENTITY(1,1),
 DateMONTH Varchar(20), 
 EmpID INT,
 Salary DECIMAL
)

 Insert Into Emp_Salary VALUES('January',1, 15000)
 Insert Into Emp_Salary VALUES('January',2, 19000)
 Insert Into Emp_Salary VALUES('January',3, 18000)
 Insert Into Emp_Salary VALUES('January',4, 15000)
 Insert Into Emp_Salary VALUES('February',1, 20000)
 Insert Into Emp_Salary VALUES('February',2, 23000)
 Insert Into Emp_Salary VALUES('February',3, 25000)
 Insert Into Emp_Salary VALUES('February',4, 27000)
 Insert Into Emp_Salary VALUES('March',1, 15000)
 Insert Into Emp_Salary VALUES('March',2, 19000)
 Insert Into Emp_Salary VALUES('March',3, 18000)
 Insert Into Emp_Salary VALUES('March',4, 15000)

Now I had to update the column TotalSalary of Emp_Info table by calculating the sum of salary of all months from Emp_Salary table for each employee and update the total salary in Emp_Info table..

The solution to this is in the below query :

 UPDATE Emp SET Emp.[TotalSalary]= EmpSalary .[Salary] From  Emp_Info Emp JOIN 
(SELECT SUM(Salary) As Salary,EmpID FROM Emp_Salary GROUP BY EmpID ) EmpSalary ON
 EmpSalary .EmpID=Emp.EmpID
  
 Select * from Emp_Info

So you can see in the above image that TotalSalary for each employee has been updated. 

That's all about how to use Update command with Join in SQL. 

Do Let me know you feedback, comments.