MySQL 如何从MySQL中的select中删除？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4562787/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to delete from select in MySQL?
提问by IAdapter
This code doesn't work for MySQL 5.0, how to re-write it to make it work
此代码不适用于 MySQL 5.0，如何重新编写以使其工作
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).
我想删除没有唯一 ID 的列。我会补充一点，大多数时候它只有一个 id（我尝试了 in 语法，但它不起作用）。
回答by BoltClock
SELECT(sub)queries return result sets. So you need to use IN, not =in your WHEREclause.
SELECT（子）查询返回结果集。所以你需要使用IN, 而不是=在你的WHERE条款中。
Additionally, as shown in this answeryou cannot modify the same table from a subquery within the same query. However, you can either SELECTthen DELETEin separate queries, or nest another subquery and alias the inner subquery result (looks rather hacky, though):
此外，如本答案所示，您不能从同一查询中的子查询修改同一表。但是，您可以SELECT然后DELETE在单独的查询中，或者嵌套另一个子查询并为内部子查询结果设置别名（虽然看起来很笨拙）：
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.
或者按照 Mchl 的建议使用连接。
回答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)
回答by Charif DZ
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
回答by havvg
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;
