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

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

6 件のコメント:

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

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

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

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

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

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

Marel さんのコメント...
このコメントは投稿者によって削除されました。
Marel さんのコメント...

Selamat datang di daftar situs judi online terbaru 2020, kali ini saya akan merekomendasikan kepada Anda semua khususnya para pemain dan pecinta judi online..

Ada banyak kelebihan dan keuntungan yang bisa Anda dapatkan secara langsung jika nantinya sudah mulai begabung dan terdaftar sebagai memmber dalam daftar situs judi online terpopuler ini..

Mengenai info lebih jelas dan lengkapnya lagi Anda bisa mengunjugi langsung situs-situs tersebut di bawah ini :

1. Togel Pakong

Togelpakong Merupakan Situs Togel Online Indonesia Yang Resmi Dan Terpercaya Juga Masuk Dalam Daftar Bandar Togel Terbesar Di Indonesia Dan Asia..

2. Joker123

Joker123 Merupakan Agen Joker Gaming Slot Joker123 Situ Judi Online Terbaru Yang Bisa Deposit Pulsa Dan Juga Bisa Menggunakan Uang Asli Via Transfer Bank..

3. RupiahBet

Rupiahbet365 Merupakan Agen Situs Judi Online uang asli untuk game Bet88 dan juga casino online,sbobet taruhan bola yang terbesar di Indonesia dan Asia 2020..

4. Togel Pakong2

Togelpakong2 Merupakan Website Togel Online Ini Merupakan Situs Online Judi Togel Terbaik Terpercaya 2019 Yang Juga Menyediakan Live Draw Sgp yang ada di Indonesia..

Dapatkan pelayanan terbaik dan juga bonus hadiah harian yang akan terus di bagikan kepada setiap member yang telah terdaftar. Maka dari itu ayo begabung dan raih kemenangan besar Anda sekarang juga bersama daftar situs judi online terbaru 2020..

Agen JudI Onlien MPO さんのコメント...

mpo99


whatsapp plus apk さんのコメント...

how to train your dragon 3 watch online free