Delete from table using a join
March 28, 2007 – 8:38 pmMySQL 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.
One Response to “Delete from table using a join”
Do you mean something like this :
delete q from QC q left join Batches b on q.STDBatch=b.Batch
where b.Batch is not null and q.TS>0;
By la abeja on May 4, 2007