如何从 MySQL 中的选择中删除?

2022-01-23 00:00:00 sql select subquery mysql uniqueidentifier

此代码不适用于 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.

另外，如 this answer 您不能从同一查询中的子查询修改同一表.但是，您可以在单独的查询中 SELECT 然后 DELETE ，或者嵌套另一个子查询并为内部子查询结果设置别名(虽然看起来很hacky):

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 的建议.

相关文章