How to Update Table Data Using Inner Join in Sql Server

In this tutorial i am going to explain How to update table data in one table based on another table using Inner Join in Sql Server.

While we work in any project 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 Operation.

Syntax of Inner Join for UPDATE Operation

UPDATE tb1
SET tb1.Column1 = tb2.Column1
FROM Table1 AS tb1 INNER JOIN Table2 AS tb2
ON tb1.Id = tb2.Id;

Hear tb1 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 take one example to understand this concept.

First we will create table.

--Create a table 'EmployeeMaster'
 
CREATE TABLE EmployeeMaster
(
            EmployeeId   INT IDENTITY(1,1) PRIMARY KEY,
            Name         VARCHAR(50),
            Salary       DECIMAL(18,2),
            Designation  VARCHAR(50)
)

After Create Table Insert some Data into Table.

--Insert some sample data in this table
 
INSERT INTO EmployeeMaster
VALUES
('Nikunj Satasiya',134000,NULL),
('Hiren Dobariya',48000,NULL),
('Pratik Pansuriya',80000,NULL),
('Sneha Patel',36000,NULL),
('Vivek Ghadiya',37000,NULL),
('Keval Pansara',22000,NULL),
('Gaurang Sondagar',12000,NULL);

Retrieve Table Data From Table.

--Check data in table
 
SELECT * FROM EmployeeMaster

Result Will be:

Now, We Will Create another table.

--Create another table 'EmployeeExperience'
 
CREATE TABLE EmployeeExperience
(
    EmployeeId          INT,
    YearsOfExperience   INT,
    WorkedAs            VARCHAR(50)
)

After Create Table Insert some Data into Table.

--Insert some sample data in this table
 
INSERT INTO EmployeeExperience
VALUES
(1,4,'.Net Developer'),
(2,7,'PHP Developer'),
(3,12,'Developer'),
(4,5,'Support'),
(5,5,'Java Developer'),
(6,3,'Support'),
(7,1,'Tester');

Retrieve Table Data From Table.

--Check data in table
 
SELECT * FROM EmployeeExperience

Result Will be:

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 'EmployeeMaster' needs to be updated with 'WorkedAs' Column from 'EmployeeExperience'.

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

Now we write 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 EmployeeMaster EM
INNER JOIN EmployeeExperience EX ON EM.EmployeeId=EX.EmployeeId

Retrieve Table Data From Updated Table.

--Check Updated data
 
SELECT * FROM EmployeeMaster

Result Will be:

• Tweet
• Share
• Share
• Share
• Share

About Nikunj Satasiya

I am a passionate programmer and a Software Engineer who love developing applications in Microsoft Technologies, blogging and other related technologies like Asp.Net, VB.Net, SEO, Sql Server,My SQL,Asp.Net-MVC, LINQ, C#.Net, Stored Procedures, Ajax, JavaScript, JQuery, xml etc.

Related Post