MySQLがupdateを実行する場合の[ERROR 1093]処理方法

本ドキュメントは転載可能で、原作者に署名しなければならない.作者:无為qq:49073687周祥興[email protected]

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

2つの解決策があります
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