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.
If you found this post helpful, please consider sharing to your network. I'm also available to help you be successful with your distributed systems! Please reach out if you're interested in working with me, and I'll be happy to schedule a free one-hour consultation.