如何删除从选择在 MySQL？

这段代码不适用于 MySQL 5.0，如何重新编写以使其正常工作

DELETE FROM posts where id=(SELECT id FROM posts GROUP BY id  HAVING ( COUNT(id) > 1 ))

我想删除没有唯一 ID 的列。我将添加大多数情况下它只有一个 id (我尝试了 in 语法，它也不能工作)。

230474 次浏览
小开
最佳答案

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)
小开

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;
小开

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

提交答案