MySQL won’t let you delete from a table using a subquery that references itself. Fair enough. To get around this, up till now, I’ve used temporary tables. However, I’ve never really liked it, and I’ve always wanted a JOIN delete. Well, apparently it exists.
I’m cleaning out some data from some really old database dump. I needed to preserve the data in the article table, but remove the rows in the city table based on a column in the article table.
delete city from city, article WHERE city.name = article.name AND article LIKE "EMPTY DATA%";
I didn’t even finish the query using the temporary table, and this one finished in about a minute. The tables had 4 million rows (article) and 95K rows (city) respectively.
There are more examples on the MySQL website.