问题描述
此代码不适用于 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，或者嵌套另一个子查询并为内部子查询结果设置别名(虽然看起来很笨拙):
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 的建议.
这篇关于如何从MySQL中的select中删除?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！