如何解决 MySQL 错误“您无法在 FROM 子句中为更新指定目标表 X”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37251621/
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 resolve MySQL error "You can't specify target table X for update in FROM clause"?
提问by mohsen.2
Why doesn't this query work?
为什么这个查询不起作用？
DELETE FROM cancome WHERE user_id IN (
SELECT user_id FROM cancome
GROUP BY user_id
HAVING COUNT(user_id)>3
)
limit 3
I get this error message:
我收到此错误消息：
[Err] 1093 - You can't specify target table 'cancome' for update in FROM clause
[Err] 1093 - 您不能在 FROM 子句中指定目标表“cancome”进行更新
回答by zoubida13
The reason why this doesn't work is that MySQL doesn't allow you to reference the table that you are updating (cancome) within a subquery.
这不起作用的原因是 MySQL 不允许您在子查询中引用您正在更新 (cancome) 的表。
This can however be overcome by using a query instead of the table itself in the FROM, which has the effect of copying the requested table values instead of referencing the one that you are updating.
然而，这可以通过在 FROM 中使用查询而不是表本身来克服，这具有复制请求的表值而不是引用您正在更新的值的效果。
So effectively this, even if counter intuitive, will work :
如此有效，即使违反直觉，也会起作用：
DELETE FROM cancome WHERE user_id IN
( SELECT user_id FROM (SELECT * FROM cancome) AS cancomesub
GROUP BY user_id HAVING COUNT(user_id)>3 )
limit 3
