Update multiple rows with different values SQL server

In this article I am going to explain how to update multiple row with different value by single Update Statement in SQL.

Here I am creating two table and by these two table I will update  employee table "salary column" value according to employee department with the help of "salary" table

CREATE TABLE Employee (                 EmpId INT identity(1, 1)                 ,NAME VARCHAR(50)                 ,Department VARCHAR(20)                 ,Salary MONEY                 )

create table salary ( Department VARCHAR(20) ,Salary MONEY )

Employee Table Data

Salary Table Data

Now I am writing a single update statement, which update employee salary according to department by the use of "salary" table.

UPDATE t1 SET t1.Salary = t2.Salary FROM Employee AS t1 INNER JOIN salary AS t2 ON t1.Department = t2.Department

After executing above  update query the employee table look like