How to delete data in SQL Server using an INNER JOIN query

Time and time again I find myself needing to do this and I always have to google it.

When I find the answer I'm still not sure and I hesitate to Execute the query.

So I decided to write this post because I know I can trust it.

When you want to delete data from one table where by data in a joined table, the first thing you should do is get the SELECT query right like this:

In this example it will SELECT all students who are in a classroom where the teacher id is 42

--Always do a SELECT query first to make sure the correct records are being returned. Notice the DELETE line is commented out.
--DELETE a
SELECT a.*
FROM tblStudent a
INNER JOIN tblClassroom b
	ON a.ClassroomId = b.Id
WHERE b.TeacherId = 42

Then when you are happy with the select query, you can uncomment the delete line and comment out the select line. 

In this example it will DELETE all students who are in a classroom where the teacher id is 42

--Now this will DELETE the matching records from table a. Notice the SELECT line is now commented out.
DELETE a
--SELECT a.*
FROM tblStudent a
INNER JOIN tblClassroom b
	ON a.ClassroomId = b.Id
WHERE b.TeacherId = 42

You can be confident that if you have followed this example then you will not delete the whole table just the records from that table which match the inner join.

If you found this post useful, please bookmark it and share it with other people.