How to delete from select in MySQL?
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.
DELETE p1 FROM posts AS p1 CROSS JOIN ( SELECT ID FROM posts GROUP BY id HAVING COUNT(id) > 1) AS p2USING (id)
you can use inner join :
DELETE ps FROM posts ps INNER JOIN (SELECT distinct id FROM posts GROUP BY id HAVING COUNT(id) > 1 ) dubids on dubids.id = ps.id