按照MYSQL5.0文档的解释:我们不能在修改表A的同时在其子查询中使用到表A,但是可以通过在子查询中在嵌套一层针对表A的子查询,因为最里层的子查询产生的结果存在临时表中,与表A没有关系。
解决方法:把类似于 UPDATE t ... WHERE col = (SELECT ... FROM t ...); 改写成UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);即可。
文档原文:In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms:
例如 UPDATE student SET student.name = 'aa' WHERE id=(SELECT __tmpTable.id FROM (SELECT...FROM t) as _tmpTable )