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.

Want to thank me?

If I've helped you out and you want to thank me, why not buy me a coffee?

Buy me a coffee

About the author

Paul Seal

Umbraco MVP and .NET Web Developer from Derby (UK) who specialises in building Content Management System (CMS) websites using MVC with Umbraco as a framework. Paul is passionate about web development and programming as a whole. Apart from when he's with his wife and son, if he's not writing code, he's thinking about it or listening to a podcast about it.

Related Posts

10 Web Developer tips I learned at Codegarden 2018

In this post I share with you 10 web developer tips and tricks I learned at Codegarden 2018

Read Post

The day I hacked Troy Hunt's website to delete my data

I needed to remove my personal details from Troy Hunt's website. This post shows you why and how I d…

Read Post

How to migrate or downgrade a SQL Server database to a lower version

This post shows you how you can migrate or downgrade a SQL Server database from one version to a low…

Read Post