December 05, 2013, 8:00 AM — 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.
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.
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