How to delete from select in MySQL?

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
)

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 p2
USING (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  

If you want to delete all duplicates, but one out of each set of duplicates, this is one solution:

DELETE posts
FROM posts
LEFT JOIN (
    SELECT id
    FROM posts
    GROUP BY id
    HAVING COUNT(id) = 1

    UNION

    SELECT id
    FROM posts
    GROUP BY id
    HAVING COUNT(id) != 1
) AS duplicate USING (id)
WHERE duplicate.id IS NULL;