Wednesday, March 30, 2016

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

No comments:

Post a Comment