MySQLでサブクエリ（エラー#1093を回避する方法）

MySQLでもサブクエリーが使えることを思い出して、とあるプロジェクトで使ってみることにした。さくっと、以下のようなSQLを書くと･･･

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

#1093 - You can't specify target table 'exam' for update in FROM clause

と怒られてしまった。
正直な話、SQLは得意というわけではないので、 最初はやっぱり無理？と思ってしまった。

が、気を取り直して「mysql 1093」でググってみると、答えのページを発見。MySQLだからではなく、SQL標準から見ても間違った構文なんだそうだ。

＊　＊　＊　＊　＊　＊　＊　＊　＊　＊

簡単に問題の説明をすると、下記のようなテーブルがあるとします。

CREATE TABLE exam (
   id SERIAL,  
   code int,
   type char(1)
)

ここでidがプライマリキーで自動でナンバーリングしてくれます。typeは、例えば試験会場とかをあらわしていて、codeには各会場での連番を振りたい、といった要望です。

サブクエリが使えないと、一旦テーブルをロックして、「type='A'」 の条件でcodeの最大値を読み出して、それを使って･･･と面倒なことをしなければいけません。

＊　＊　＊　＊　＊　＊　＊　＊　＊　＊

で、そのページ曰く、SQLではあるテーブルに対してデータを追加･更新する場合、同じテーブルをサブクエリーに使えないのだそうです。

それじゃ、意味ないじゃない！
と思ったが、続きがあって、上手にFROM句を使う方法が書いてあった。SQLでは、サブクエリー内のFrom句はテンポラリテーブルとして扱うことが可能で、次のように書き換えれば動くとのこと。

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

何をしているのかというと、
まさにサブクエリー内のFromを
「SELECT MAX( code ) AS max_code FROM table WHERE type='A'」
というクエリで書き換えて、それをtemp1とか名前をつけてます（つけないと怒られます）。
そこから「max_code+1」で最大値を読み取ってます。ちなみにIFNULLがあるのは、データが無い場合にNULLが帰ってきてしまうから。

これで動きました。テーブル名と項目名は適宜書き換えているので、ミスっているかも知れません。ともあれ初めて真面目にサブクエリーを使ったのでメモしておきます。