SQL - Update table using Inner join in SQL

We always try to keep normalization in our database and maintain table relationship for each record as possible. To maintain normalization, we always put our records in more than two tables by making relationship between them which are highly tide up mostly on primary and foreign key relationship.

Example: In an organization, based on the performance, some employees got the appraisal but some of them did not get any appraisal. Now, system needs to update the salary in employee master only for those employees who got the appraisal.

Note: We can only update 1 table at a time by using Update command.

We have two tables structure to store the above scenario such as:

USE tempdb; GO ------ create temp table to store employees information CREATE TABLE [dbo].[EmployeeMaster](        [EmpId] [int] IDENTITY(100,1) NOT NULL,        [EmpName] [varchar](50) NOT NULL,        [EmpSalary] [int] NOT NULL,        [EmpAge] [int] NOT NULL,        [EmpStatus] [bit] NULL ) ON [PRIMARY] ------ create another temp table to store employees appraisal CREATE TABLE [dbo].[AppraisedEmployee] (        [EmpId] [int] NOT NULL,        [AppraisedSalary] [int] NOT NULL ) ON [PRIMARY] ------ both tables have the relation on EmpId

Insert records into Employee Master-

----- Insert Values in the employees Master table Insert into EmployeeMaster(EmpName, EmpSalary, EmpAge) Values ('Amit Gupta',15000,32), ('Anil Sharma',15500,22), ('Bill Smith',16500,25), ('Chris Gray',17500,28), ('David King',15500,22), ('Eliza Roy',15500,25), ('Flash God',15500,23)

Records into Employee Master

------ Records in the Employee Master table Select * from dbo.EmployeeMaster EmpId EmpName EmpSalary EmpAge EmpStatus 100 Amit Gupta 15000 32 NULL 101 Anil Sharma 15500 22 NULL 102 Bill Smith 16500 25 NULL 103 Chris Gray 17500 28 NULL 104 David King 15500 22 NULL 105 Eliza Roy 15500 25 NULL

Based on the yearly performance, Bill and David got the appraisals only and records in the Appraised Employee Table –

------ insert data into employees appraisal table Insert into [dbo].[AppraisedEmployee] (EmpId, AppraisedSalary) values (102,18000), (104,17500) ------ Records in the employees appraisal table Select * from [dbo].[AppraisedEmployee] EmpId AppraisedSalary 102 18000 104 17500

We need to update Employee Mater table for appraised employees by using the any one following approaches –

Use Update command such as –

----- by Using Set Based Approch Update [dbo].[EmployeeMaster] set [EmpSalary]= am.AppraisedSalary from [dbo].[EmployeeMaster] em inner join [dbo].[AppraisedEmployee] am on em.EmpId = am.EmpId ------ Updated Records in the Employee Master table Select * from dbo.EmployeeMaster EmpId EmpName EmpSalary EmpAge EmpStatus 100 Amit Gupta 15000 32 NULL 101 Anil Sharma 15500 22 NULL 102 Bill Smith 18000 25 NULL 103 Chris Gray 17500 28 NULL 104 David King 17500 22 NULL 105 Eliza Roy 15500 25 NULL

Use Merge Statements– This feature was introduced in SQL from SQL Server 2008 and onwards.

The MERGE statement allows you to join a data source with a target table or view, and then perform multiple actions against the target based on the results of that join.

This is the way where we are able to view the updated records such as –

---- EmployeeMaster will be act as Target table where records need to be updated MERGE EmployeeMaster AS EM ---- AppraisedEmployee will be act as Source table from where records need to be picked USING AppraisedEmployee AS AE ON (EM.EmpId = AE.EmpId) WHEN MATCHED     THEN UPDATE SET EM.EmpSalary= AE.AppraisedSalary ------ Show the action on the table OUTPUT $action, inserted.*; $action EmpId EmpName EmpSalary EmpAge EmpStatus UPDATE 102 Bill Smith 18000 25 NULL UPDATE 104 David King 17500 22 NULL ------ Updated Records in the Employee Master table Select * from dbo.EmployeeMaster EmpId EmpName EmpSalary EmpAge EmpStatus 100 Amit Gupta 15000 32 NULL 101 Anil Sharma 15500 22 NULL 102 Bill Smith 18000 25 NULL 103 Chris Gray 17500 28 NULL 104 David King 17500 22 NULL 105 Eliza Roy 15500 25 NULL

If you have duplicates records than Merge statement will generate the error.
Query at the Glance-

USE tempdb; GO ------ create temp table to store employees information CREATE TABLE [dbo].[EmployeeMaster](        [EmpId] [int] IDENTITY(100,1) NOT NULL,        [EmpName] [varchar](50) NOT NULL,        [EmpSalary] [int] NOT NULL,        [EmpAge] [int] NOT NULL,        [EmpStatus] [bit] NULL ) ON [PRIMARY] ------ create another temp table to store employees appraisal CREATE TABLE [dbo].[AppraisedEmployee] (        [EmpId] [int] NOT NULL,        [AppraisedSalary] [int] NOT NULL ) ON [PRIMARY] ------ both tables have the relation on EmpId ----- Insert Values in the employees Master table Insert into EmployeeMaster(EmpName, EmpSalary, EmpAge) Values ('Amit Gupta',15000,32), ('Anil Sharma',15500,22), ('Bill Smith',16500,25), ('Chris Gray',17500,28), ('David King',15500,22), ('Eliza Roy',15500,25), ('Flash God',15500,23) ------ Records in the Employee Master table Select * from dbo.EmployeeMaster ------ insert data into employees appraisal table Insert into [dbo].[AppraisedEmployee] (EmpId, AppraisedSalary) values (102,18000), (104,17500) ------ Records in the employees appraisal table Select * from [dbo].[AppraisedEmployee] /* UPDATE STATEMENT*/ ----- by Using Set Based Approch Update [dbo].[EmployeeMaster] set [EmpSalary]= am.AppraisedSalary from [dbo].[EmployeeMaster] em inner join [dbo].[AppraisedEmployee] am on em.EmpId = am.EmpId ------ Updated Records in the Employee Master table Select * from dbo.EmployeeMaster /* MERGE STATEMENT*/ ---- EmployeeMaster will be act as Target table where records need to be updated MERGE EmployeeMaster AS EM ---- AppraisedEmployee will be act as Source table from where records need to be picked USING AppraisedEmployee AS AE ON (EM.EmpId = AE.EmpId) WHEN MATCHED     THEN UPDATE SET EM.EmpSalary= AE.AppraisedSalary ------ Show the action on the table OUTPUT $action, inserted.*; ------ Updated Records in the Employee Master table Select * from dbo.EmployeeMaster -----drop tables drop table EmployeeMaster, AppraisedEmployee;

Please watch our demo video at YouTube-

To learn more, please follow us - 🔊 http://www.sql-datatools.com To Learn more, please visit our YouTube channel at — 🔊 http://www.youtube.com/c/Sql-datatools To Learn more, please visit our Instagram account at - 🔊 https://www.instagram.com/asp.mukesh/ To Learn more, please visit our twitter account at - 🔊 https://twitter.com/macxima

Now, with the help of above example, you can easily update your table by using inner join more than two tables. You must be visit also SQL - Delete using INNER JOIN in SQL.