How to delete database rows with a JOIN statement

SQL delete rows with join

By  

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.

Don't miss...

Why you should jailbreak your Apple TV - and how to do it
Why you should jailbreak your Apple TV - and how to do it
Apple iOS app review - frustrating and bad for your health
Apple iOS app review - frustrating and bad for your health
The best home backup plan options
The best home backup plan options
Join us:
Facebook

Twitter

Pinterest

Tumblr

LinkedIn

Google+

Answers - Powered by ITworld

Ask a Question