如何从MySQL中的选择中删除? [英] How to delete from select in MySQL?

问题描述

此代码不适用于MySQL 5.0，如何重写使其正常工作

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

我想删除没有唯一ID的列.我会在大多数情况下添加唯一的一个ID(我尝试了in语法，它也不起作用).

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).

推荐答案

SELECT(子)查询返回结果 sets .因此，您需要在WHERE子句中使用IN，而不是=.

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
)

或按照Mchl的建议使用连接

Or use joins as suggested by Mchl.

这篇关于如何从MySQL中的选择中删除?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！