Please disable your adblock and script blockers to view this page
 

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.


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. 
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily.. ConversionConversion EmoticonEmoticon