Tuesday, May 26, 2015

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;

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.

5 comments:

  1. Hi mukesh,

    if you will use alias in update command that will be more batter.

    ReplyDelete
  2. --Updating table using alias
    Update em
    set em.[EmpSalary] = am.AppraisedSalary
    from [dbo].[EmployeeMaster] em
    inner join [dbo].[AppraisedEmployee] am
    on em.EmpId = am.EmpId

    ReplyDelete
    Replies
    1. Thanks Brajesh to explain the example with alias.

      Delete

Popular Posts