Update table data using INNER JOIN in SQL SERVER.

Introduction: In this article I have explained How to update data in one table based on other table using Sql Server Inner Join.


Description: Many times we need to update columns in one table based on the columns in another table .In such case we can use UPDATE statement by joining tables together using INNER JOIN.

Syntax for Update with Inner Join

UPDATE T1
SET T1.Column1 = T2.Column1
FROM Table1 AS T1 INNER JOIN Table2 AS T2
ON T1.Id = T2.Id;

T1 is an alias name for Table1, whose rows we want to update based on matching rows with Table2. On clause specifies the column names to find matching rows between both tables using Inner Join. SET specifies that Column1 Table1 value will be updated with values of the Column1 of Table2. Multiple columns can also be updated.

Implementation: Let's understand with an example.

--Create a table 'tbEmployeeMaster'

CREATE TABLE tbEmployeeMaster
(
            EmployeeId    INT IDENTITY(1,1) PRIMARY KEY,
            Name              VARCHAR(100),
            Salary              DECIMAL(18,2),
            Designation    VARCHAR(50)
)

--Insert some sample data in this table

INSERT INTO tbEmployeeMaster
VALUES
('Aman',34000,NULL),
('Rohan',48000,NULL),
('Varun',80000,NULL),
('Arjun',37000,NULL),
('Raghav',22000,NULL),
('Sameer',12000,NULL);

--Check data in table

SELECT * FROM tbEmployeeMaster

Result will be:
EmployeeId
Name
Salary
Designation
1
Aman
34000.00
NULL
2
Rohan
48000.00
NULL
3
Varun
80000.00
NULL
4
Arjun
37000.00
NULL
5
Raghav
22000.00
NULL
6
Sameer
12000.00
NULL

--Create another table 'tbEmployeeExperience'

CREATE TABLE tbEmployeeExperience
(
            EmployeeId                INT,
            YearsOfExperience     INT,
            WorkedAs                   VARCHAR(50)
)

--Insert some sample data in this table

INSERT INTO tbEmployeeExperience
VALUES
(1,4,'Quality Analyst'),
(2,7,'Tester'),
(3,12,'Developer'),
(4,5,'Developer'),
(5,3,'Support'),
(6,1,'Tester');

--Check data in table

SELECT * FROM tbEmployeeExperience

Result will be:
EmployeeId
YearsOfExperience
WorkedAs
1
4
Quality Analyst
2
7
Tester
3
12
Developer
4
5
Developer
5
3
Support
6
1
Tester

Now let us suppose employee salary is to be incremented based on their experience as per following criteria.
  • Increment salary by 10000 If employee have more than 10 years of experience.
  • Increment salary by 5000 If employee have 6 to 10 years of experience.
  • Increment salary by 2000 If employee have experience up to 5 years.
  • And also 'Designation' Column of 'tbEmployeeMaster' needs to be updated with 'WorkedAs' Column from 'tbEmployeeExperience'.


So we need to update employee salary in 'tbEmployeeMaster' table based on their years of experience value from 'tbEmployeeExperience' table.


Update query using Inner Join 

UPDATE EM
SET EM.Designation=EX.WorkedAs,
EM.Salary=CASE
WHEN EX.YearsOfExperience >10 THEN (EM.Salary + 10000)
WHEN EX.YearsOfExperience >= 6 AND EX.YearsOfExperience<=10 THEN (EM.Salary + 5000)
ELSE (EM.Salary + 2000) END
FROM tbEmployeeMaster EM
INNER JOIN tbEmployeeExperience EX ON EM.EmployeeId=EX.EmployeeId

--Check Updated data

SELECT * FROM tbEmployeeMaster

Result will be:
EmployeeId
Name
Salary
Designation
1
Aman
36000.00
Quality Analyst
2
Rohan
53000.00
Tester
3
Varun
90000.00
Developer
4
Arjun
39000.00
Developer
5
Raghav
24000.00
Support
6
Sameer
14000.00
Tester

 Now over to you:
A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates. 
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..