Delete from table using a join

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.