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).
This question is related to
sql
mysql
select
uniqueidentifier
subquery
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
DELETE
p1
FROM posts AS p1
CROSS JOIN (
SELECT ID FROM posts GROUP BY id HAVING COUNT(id) > 1
) AS p2
USING (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;
Source: Stackoverflow.com