此代码不适用于MySQL 5.0,如何重写才能使其工作
DELETE FROM posts where id=(SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 ))
我想删除不具有唯一id的列。我会补充说,大多数情况下,它只有一个id(我尝试了in语法,但它不起作用)。
发布于 2010-12-30 21:12:34
DELETE
p1
FROM posts AS p1
CROSS JOIN (
SELECT ID FROM posts GROUP BY id HAVING COUNT(id) > 1
) AS p2
USING (id)
发布于 2017-04-01 00:43:14
您可以使用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
发布于 2013-10-10 22:02:34
如果您想删除所有重复项,但每组重复项中只删除一个,则这是一种解决方案:
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;
https://stackoverflow.com/questions/4562787
复制相似问题