MySQLのエラー#1093 同じテーブルをサブクエリに使えない問題の対策

データベース

2018.11.082017.06.23

ある時、MySQLでupdate文を実行しようとしたら、以下のエラーメッセージが出ました。

Error 1093 (ER_UPDATE_TABLE_USED)
SQLSTATE = HY000
Message = “You can’t specify target table ‘t1’
for update in FROM clause”

このエラーは何なんでしょうか？

Error 1093 (ER_UPDATE_TABLE_USED) とは
Error 1093 (ER_UPDATE_TABLE_USED) の回避方法

Error 1093 (ER_UPDATE_TABLE_USED) とは

どうやらこのエラーは、MySQLのみならず、データベースとしては、ごく普通の特性のようです。

MySQLのマニュアルには次の記述がありました。

以下のSQLは、

Error 1093 (ER_UPDATE_TABLE_USED)

となります。

INSERT INTO t1( type, code ) VALUES ( 
'A', ( 
    SELECT IFNULL( MAX( code ) + 1, 1000 ) 
    FROM t1 WHERE type='A' )
);

INSERT INTO t1( type, code ) VALUES (

'A', (

SELECT IFNULL( MAX( code ) + 1, 1000 )

FROM t1 WHERE type='A' )

);

理由は

サブクエリーは SELECT ステートメントだけでなく、UPDATE および DELETE ステートメント内でも正当であるため、UPDATE ステートメント内の割り当てのためにサブクエリーを使用できます。ただし、サブクエリーの FROM 句と更新のターゲットの両方に同じテーブル (この場合 t1) を使用することはできません。

です。

では、どうすれば、このエラーを回避できるのでしょうか。

Error 1093 (ER_UPDATE_TABLE_USED) の回避方法

SQLでは、サブクエリー内のFrom句はテンポラリテーブルとして扱うことが可能で、次のように書き換えれば動きます。

サブクエリー内のFromを

「SELECT MAX( code ) AS max_code FROM table WHERE type='A'」
というクエリで書き換えて、それをtemp1とか名前をつけてます。

INSERT INTO t1 ( type, code ) VALUES ( 
'A', ( 
   SELECT IFNULL( max_code + 1, 10001 ) 
   FROM ( SELECT MAX( code ) AS max_code FROM t1 WHERE type='A' ) AS temp1 )
);

INSERT INTO t1 ( type, code ) VALUES (

'A', (

SELECT IFNULL( max_code + 1, 10001 )

FROM ( SELECT MAX( code ) AS max_code FROM t1 WHERE type='A' ) AS temp1 )

);

おお。

これで、サブクエリ―出来ますね。