Deleting rows with a LEFT JOIN in MySQL
August 9, 2015•150 words
This is something I don't do that often, and I always forget the correct syntax. Thus this post.
Let's say you have a table images and a table users. Now you want to delete all rows in table images that are associated with rows in the table users that are marked as deleted.
First, I usually do a visual inspection of the rows that will be deleted:
SELECT i.* FROM images i LEFT JOIN users u ON i.user_id = u.id WHERE u.deleted = 1;
The rows returned by this query are the rows that will be deleted when you run the following query:
DELETE i FROM images i LEFT JOIN users u ON i.user_id = u.id WHERE u.deleted = 1;
They key part here is to specify which table you want to delete from. In this case it's the table i, which is the images table.