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.

In previous articles i explained the How to Delete Records from table using Inner Join in Sql Server and Query to search any text in all stored procedures, views and functions and CTE recursive query to get employee manager hierarchy with level and Query to find all foreign keys references of particular table and Use of self join in sql server with example and Autogenerate auto incremented unique alphanumeric id or number in sql server   

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. 

• Tweet
• Share
• Share
• Share

Related Post