How to delete database rows with a JOIN statement

SQL delete rows with join

Here's a quick and simple tip to delete database rows when a JOIN statement is required to filter the data. This question pops up more than you might think as it's not totally obvious at first.

A typical SQL delete statement on a single database table might look like this:

DELETE * FROM Subscriber WHERE OptOut = 1

This would delete every subscriber record who has chosen to opt out.

Problem

But what if you want to delete Subscribers based on data from a different table, such as a Subscription table which may hold expired Subscriptions?

Your first thought would probably be to execute this statement:

DELETE * FROM Subscriber LEFT JOIN Subscription on Subscriber.ID = Subscription.SubscriberID WHERE Subscription.IsExpired = 1

However that query will give you an SQL Syntax error.

Solution

The reason for the error is that the database doesn't know which table to apply the delete to. The simple solution is to specify the table to perform the Delete on:

DELETE Subscriber FROM Subscriber LEFT JOIN Subscription on Subscriber.ID = Subscription.SubscriberID 

WHERE Subscription.IsExpired = 1

Read more of Matthew Mombrea's ByteStream blog and follow Matt on Twitter (@mombrea) and Google+. For the latest IT news, analysis and how-tos, follow ITworld on Twitter and Facebook.

Top 10 Hot Internet of Things Startups
Join the discussion
Be the first to comment on this article. Our Commenting Policies