How to delete from select in MySQL?

Question

This code doesn't work for MySQL 5.0, how to re-write it to make it work

DELETE FROM posts where id=(SELECT id FROM posts GROUP BY id  HAVING ( COUNT(id) > 1 )) 

I want to delete columns that dont have unique id. I will add that most of the time its only one id(I tried the in syntax and it doesnt work as well).

Answer 1

SELECT (sub)queries return result sets. So you need to use IN, not = in your WHERE clause.

Additionally, as shown in this answer you cannot modify the same table from a subquery within the same query. However, you can either SELECT then DELETE in separate queries, or nest another subquery and alias the inner subquery result (looks rather hacky, though):

DELETE FROM posts WHERE id IN (     SELECT * FROM (         SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 )     ) AS p ) 

Or use joins as suggested by Mchl.

Answer 2

DELETE    p1   FROM posts AS p1  CROSS JOIN (   SELECT ID FROM posts GROUP BY id HAVING COUNT(id) > 1 ) AS p2 USING (id)