MySQL执行update时的[ERROR 1093]处理方法

来源：互联网发布：股票自动止损软件知乎编辑：程序博客网时间：2024/06/05 04:37

>update TEST_NOIDX  set CREATETIME=now() where ID in ( select a.ID from TEST_NOIDX a where a.VNAME='Aa');ERROR 1093 (HY000): You can't specify target table 'TEST_NOIDX' for update in FROM clause>update TEST_NOIDX b set b.CREATETIME=now() where b.ID in ( select a.ID from TEST_NOIDX a where a.VNAME='Aa');ERROR 1093 (HY000): You can't specify target table 'b' for update in FROM clause

从oracle转mysql的同志们，估计都会遇到上面这种情况，怎么这样的sql执行不了。

为什么会这样？

字面意思就是update的表不能出现在from语句中，原因是mysql对子查询的支持是比较薄弱的。

而且手册上面说下面的这些情况都会报错

·  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 ...);

两种解决方法，

1.改成inner join,手册上的方法。

2.多加一个嵌套。

>update TEST_NOIDX set CREATETIME = now() where ID in (select id from ( select id from TEST_NOIDX where VNAME ='Aa') aa);Query OK, 2 rows affected (0.05 sec)Rows matched: 2  Changed: 2  Warnings: 0>update TEST_NOIDX b  inner join  ( select a.ID,a.CREATETIME from TEST_NOIDX a where a.VNAME='Aa') c on b.ID=c.ID set b.CREATETIME=now();Query OK, 2 rows affected (0.04 sec)Rows matched: 2  Changed: 2  Warnings: 0

0 0