如何从MySQL中删除select ?

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(我尝试了语法，但它也没有效果)。

4 个解决方案

#1

152

SELECT (sub)queries return result sets. So you need to use IN, not = in your WHERE clause.

选择(子)查询返回结果集。所以你需要在WHERE子句中使用IN，而不是=。

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.

或使用Mchl建议的连接。

#2

DELETE 
  p1
  FROM posts AS p1 
CROSS JOIN (
  SELECT ID FROM posts GROUP BY id HAVING COUNT(id) > 1
) AS p2
USING (id)

#3

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

#4

-1

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;

#1

152