ERROR 1093 (HY000): You can't specify target table 'a' for update in FROM clause

开发的问我一个mysql 执行update语句报错的问题
模拟测试语句如下：
mysql> update g10 a set a.l = (select max(b.l) + 1 from g10 b where b.c = 1 and b.p = 3 )
    -> where a.c = 1 and a.p = 3;

报错如下
ERROR 1093 (HY000): You can't specify target table 'a' for update in FROM clause

mysql对这种update有个限制，是不能直接更新的
文档解释如下：
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:

DELETE FROM t WHERE ... (SELECT ... FROM t ...);UPDATE t ... WHERE col = (SELECT ... FROM t ...);{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);

Exception: The preceding prohibition does not apply if you are using a subquery for the modified table in the FROM

clause. Example:

UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);

Here the prohibition does not apply because a subquery in the FROM
clause is materialized as a temporary table, so the relevant rows in t
have already been selected by the time the update to t

可以这样改写一下sql：
mysql> update g10 a set a.l = (select c.l from (select max(b.l) + 1  l from g10 b where b.c = 1 and b.p = 3 ) c)
    -> where a.c = 1 and a.p = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

执行成功。




































update g10 a set a.l = (select (select max(b.l) + 1  l from g10 b where b.c = 1 and b.p = 3 ) as c)
where a.c = 1 and a.p = 3;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23249684/viewspace-1309042/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23249684/viewspace-1309042/