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).
解决方案
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
)