Delete Records from Table using INNER JOIN in SQL SERVER

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

In previous articles i explained How to Insert multiple records in table in single insert statement and Convert or split comma separated string into table rows in sql server and Backup and restore sql server database and Example to use cross join or cartesian join in sql server and Difference between temporary table and table variable in sql server

Description: Many times we need to delete records from one table based on the matching column in another table .In such case we can use DELETE statement to delete matching records using INNER JOIN.

Syntax for Delete with Inner Join

DELETE T2

FROM Table2 AS T2 INNER JOIN Table1 AS T1

ON T2.Id = T1.Id;

T2 is an alias name for Table2, whose rows we want to delete based on matching rows with Table1. On clause specifies the column names to find matching rows between both tables using Inner Join. We can delete data from any one of the two tables participating in inner join. Deletion from both participating tables is yet not supported in SQL.

--Create a table 'tbEmployeeMaster'

CREATE TABLE tbEmployeeMaster

(

            EmployeeId     INT IDENTITY(1,1) PRIMARY KEY,

            Name              VARCHAR(100),

            Salary              DECIMAL(18,2)

)

--Insert some sample data in this table

INSERT INTO tbEmployeeMaster

VALUES

('Aman',34000),

('Rohan',48000),

('Varun',80000),

('Arjun',37000),

('Raghav',22000),

('Sameer',12000);

--Check data in table

SELECT * FROM tbEmployeeMaster

EmployeeId	Name	Salary
1	Aman	34000.00
2	Rohan	48000.00
3	Varun	80000.00
4	Arjun	37000.00
5	Raghav	22000.00
6	Sameer	12000.00

--Create another table 'tbEmployeeExperience'

CREATE TABLE tbEmployeeExperience

(

            EmployeeId                INT,

            YearsOfExperience     INT     

)

--Insert some sample data in this table

INSERT INTO tbEmployeeExperience

VALUES

(1,4),

(2,7),

(3,12),

(4,5),

(5,3),

(6,1);

--Check data in table

SELECT * FROM tbEmployeeExperience 

EmployeeId	YearsOfExperience
1	4
2	7
3	12
4	5
5	3
6	1

Now let’s suppose we want to delete those employees record from 'tbEmployeeExperience' table based on the Salary column of 'tbEmployeeMaster' table where Salary is less than equal to 35000.

Delete query using Inner Join 

DELETE EX

FROM tbEmployeeExperience EX

INNER JOIN tbEmployeeMaster EM ON EX.EmployeeId=EM.EmployeeId

WHERE EM.Salary<=35000

--Check  data in table after deletion.

SELECT * FROM tbEmployeeExperience

EmployeeId	YearsOfExperience
2	7
3	12
4	5

As you can see the record of the employees getting salary less than 35000 got deleted.

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