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.

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.

Proudly sponsored by

Moriyama

  • Moriyama build, support and deploy Umbraco, Azure and ASP.NET websites and applications.
AppVeyor

  • CI/CD service for Windows, Linux and macOS
  • Build, test, deploy your apps faster, on any platform.
stkrs

  • Custom stickers for startups, artists and brands.
  • Bespoke easy-apply, removable, custom brand stickers printed in the UK.
elmah.io

  • elmah.io is the easy error logging and uptime monitoring service for .NET.
  • Take back control of your errors with support for all .NET web and logging frameworks.
uSync Complete

  • uSync.Complete gives you all the uSync packages, allowing you to completely control how your Umbraco settings, content and media is stored, transferred and managed across all your Umbraco Installations.