Using SQL to delete rows from a table using INNER JOIN to another table

Oftentimes, one wants to delete some records from a table based on criteria in another table. How do you delete from one of those tables without removing the records in both table?
DELETE DeletingFromTable
         FROM DeletingFromTable INNER JOIN CriteriaTable
         ON DeletingFromTable.field_id = CriteriaTable.id
         WHERE CriteriaTable.criteria = "value";
The key is that you specify the name of the table to be deleted from as the SELECT. So, the JOIN and WHERE do the selection and limiting, while the DELETE does the deleting. You're not limited to just one table, though. If you have a many-to-many relationship (for instance, Magazines and Subscribers, joined by a Subscription) and you're removing a Subscriber, you need to remove any potential records from the join model as well.
        DELETE subscribers, subscriptions
         FROM subscribers INNER JOIN subscriptions 
           ON subscribers.id = subscriptions.subscriber_id
         INNER JOIN magazines 
           ON subscriptions.magazine_id = magazines.id
         WHERE subscribers.name='Wes';
        
Deleting records with a join could also be done with a LEFT JOIN and a WHERE to see if the joined table was NULL, so that you could remove records in one table that didn't have a match (like in preparation for adding a relationship.) Example post to come.

Established 2005 · Databasically © 2016