如何解决MySQL错误“您无法在FROM子句中指定目标表X进行更新"? [英] How to resolve MySQL error "You can't specify target table X for update in FROM clause"?

问题描述

该查询为什么不起作用?

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

我收到此错误消息:

[Err] 1093-您无法在FROM子句中指定目标表'cancome'进行更新

[Err] 1093 - You can't specify target table 'cancome' for update in FROM clause

推荐答案

之所以不起作用，是因为MySQL不允许您引用要在子查询中更新(访问)的表.

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.

但是，可以通过在FROM中使用查询而不是表本身来克服这一问题，它具有复制请求的表值而不是引用要更新的表值的作用.

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.

即使反直观，这也将有效:

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

这篇关于如何解决MySQL错误“您无法在FROM子句中指定目标表X进行更新"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！