2009年12月1日火曜日

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が帰ってきてしまうから。

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

2 件のコメント:

てらら さんのコメント...

助かりました。
ちょうど、UPDATEで同様に連番を追加する処理をしようとして、詰まってたんです。

できたはず…でも、どうだったけ…で探してて、この記事にたどり着きました。

おかげで解決できました。ありがとうございます。

匿名 さんのコメント...

私も助かりました。
ありがとうございました。