ここはとある街の総合病院。

ここには通常の診療科のほかに、一風変わった診療科が存在する。

何軒もの病院をたらいまわしにされた、手の施しようのないSQLや、今すぐに改善が必要なSQLが担ぎ込まれる救命室である。

それがSQL緊急救命室、略してSER（SQL Emergency Room⁠）⁠。

そう、ここは国内でも唯一のプログラミング専門外来である。

ロバート

救命室部長。腕の立つエンジニアだが、口が悪く性格はもっと悪い四十オヤジ。

（AM9:00　病院の入り口。ワイリーが扉の前をウロウロしている）

（郵便配達人を見て）あっ！ やぁやぁご苦労様。はいはい受け取っておくよ……（郵便物を物色して）うーん違うな。（⁠郵便受けを開けて中を調べる）うーん、ないなぁ。まだかなぁ。

（ロバートが出勤し、ワイリーを見つける）

なんだ、ゴミ拾いにでも転職するつもりか。なんなら推薦状書くぞ。

あっ先生……！ 違いますよ。専門課程の合格通知、今日届くんですよ。ああ、どうしよう。ドキドキが止まらない。

倒れたらすぐに献体として使ってやる。それがお前の医学にできる最大の貢献だ。

（聞いていない）ああ、どうしよう。学資ローンもまだ残っているし、これで通らなかったら……。

ふう……まったく。いい加減そのぐらいにして、早く来い！患者が待っているぞ！

更新における冗長なサブクエリ

（治療室。ヘレンがいる。）

遅いじゃないの。先にはじめてたわよ。

すまんな。こいつが道草を食っていたせいでな。……まったく。上の空だな。早く気持ちを切り替えろ。

すいません。どうも不安で。

ああ、そういえば今日だったわね。合格通知。なるほど、そりゃ落ち着かないわけね。でも仕事は仕事よ。

はい。

代入式への行式の拡張

患者のコード（リスト1）は、機能的には間違いじゃありませんよね。

UPDATE OrderDetails
   SET item     = (SELECT item
                     FROM EntryDetails AS ED
                    WHERE OrderDetails.entry_id = ED.entry_id
                      AND OrderDetails.entry_seq = ED.entry_seq),
       quantity = (SELECT quantity
                     FROM EntryDetails AS ED
                    WHERE OrderDetails.entry_id = ED.entry_id
                      AND OrderDetails.entry_seq = ED.entry_seq);

間違いじゃないわ。結果は正しく更新されるわ。

ふむ。とすると問題は……。

これまでの治療で見てきた症状を挙げていってみなさい。

ええっと、最初に見たのが「サブクエリ・パラノイア⁠」⁠。でもこのSQLでサブクエリを使わずに解くのは無理ですよね。次が「冗長性症候群⁠」⁠。……たしかに、このSQLはほとんど同じサブクエリを2回繰り返していますね。

このサブクエリは明らかに冗長だな。まあ、まだ1つのSQLで書いているだけこの患者はましなほうだ。ひどいのになるとSQL自体を分割したりするからな。

でもどうやって直すんですか？

前回勉強したでしょう。行式よ（リスト2⁠）⁠。

UPDATE OrderDetails
   SET (item, quantity)
           = (SELECT item, quantity
                     FROM EntryDetails ED
                    WHERE OrderDetails.entry_id = ED.entry_id
                      AND OrderDetails.entry_seq = ED.entry_seq);

へえ！ UPDATE文のSET句でも行式は使えるのですね。

逆に聞くけど、なぜ「使えない」って思った？

いやそれは……。そんないじめないでくださいよ、えへへ。

気色悪い……。

シンプルさは常に良い

ヘレンが示したとおり、行式はUPDATE文のSET句でも使えます。比較式で利用できるのだから、代入式でも利用できるのは、考えてみれば当然の拡張なのですが、意外に知られていなかったり、忘れられていたりします。

その意味で、この患者のコード（リスト1）は、冗長性症候群とともに時代錯誤症候群も併発しています。SQLにおいて、コードが冗長であったり時代遅れであったりして良いことはまずありません。その証拠に、それぞれの実行計画を見てみましょう（図4、図5⁠）⁠。

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |              |     6 |   126 |       3 (0)| 00:00:01 |
|   1 |  UPDATE                      | ORDERDETAILS |       |       |            | |
|   2 |   TABLE ACCESS FULL          | ORDERDETAILS |     6 |   126 |       3 (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| ENTRYDETAILS |     1 |    12 |       1 (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | SYS_C004301  |     1 |       |       0 (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| ENTRYDETAILS |     1 |    14 |       1 (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN         | SYS_C004301  |     1 |       |       0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("ED"."ENTRY_ID"=:B1 AND "ED"."ENTRY_SEQ"=:B2)
   6 - access("ED"."ENTRY_ID"=:B1 AND "ED"."ENTRY_SEQ"=:B2)

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |              |    6 |   216 |       3 (0)| 00:00:01 |
|   1 |  UPDATE                      | ORDERDETAILS |      |       |            |          |
|   2 |   TABLE ACCESS FULL          | ORDERDETAILS |    6 |   216 |       3 (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| ENTRYDETAILS |    1 |    36 |       1 (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | SYS_C004301  |    1 |       |       1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("ED"."ENTRY_ID"=:B1 AND "ED"."ENTRY_SEQ"=:B2)

患者の解は、「⁠受注明細」テーブル（EntryDetails）に対して2つのサブクエリを実行しているため、実行計画においても2度のアクセスが発生しています。一方、ヘレンの解では「受注明細」テーブルへのアクセスが1回に減っています。それだけ、パフォーマンスも向上するわけです。コードから冗長性をなくすことには、可読性を上げる以上のメリットが常にあります。本連載第4回の冒頭に掲げた「KISSの原則」（⁠Keep It Simple, Stupid.）を覚えているでしょうか？

なお、このサンプルでは「受注明細」テーブルへのアクセスには同テーブルの主キーのインデックスへのユニークアクセス（INDEX UNIQUE SCAN）が行われているため、サブクエリのアクセスコストはそれほど高くありません。しかし、いつもこのようにうまくインデックスが使えるわけではありません。

残念なお知らせ

この便利な行式ですが、代入において利用できるDBMSは、現在のところOracleとDB2のみです[1]⁠。標準SQLの機能であるため、気長に待っていればいずれはサポートされていくのでしょうが、サブクエリを代入式の引数に取る場合のパフォーマンスを大きく改善できる機能であるため、早期のサポートが望まれるところです。

SQL での更新は、いろいろ制約が多いと聞きますが、なぜなのでしょう。

SQL がもともと更新機能をあまり重視していなかったことが影響しているのだろうな。ワイリー、SQLの「Q」はどういう意味だ？

知りません。（⁠ｷﾘｯ

聞いたワシがバカだった……。

Structured Query Language、Q は「Query」（⁠問い合わせ）の略。つまりSELECT文よ。

なるほど。もともとがSQLはSELECT文中心の言語だったわけですね。でもSQLってけっこう、大量データの更新にも使われますよね。

うむ、最近はバッチ処理などで大量データを一括更新する用途で使われるのも珍しくない。MERGE文や複数行INSERT文など、更新機能も徐々に充実してきているが、まだまだ十分とは言いがたいな。

検索SQL においては、極力選択条件でレコードを絞り込むことが常識になっている。だが更新SQLでは、この点が意外に守られていないケースがある。

というと？

SET句の役割を勘違いすることに起因するケースだ。次の患者を見てみよう。

SET句は更新対象を制限しない

これ、さっきの問題と何か違うんですか？ 同じUPDATE 文でいいような気がするんですけど。

更新結果の正しさだけを求めるなら、さっきの解がそのまま使えるわ。問題はパフォーマンスよ。

カルテ1と同様に解くと、このカルテ2では「発注明細」テーブルを何行更新する？

えっと……「発注明細」テーブルで受注明細のレコードと一致するのは4行だから、4行？

ブー。答えは6行。つまり全レコードよ。

このケースにおいては、一見すると更新対象は4行であるように錯覚します。確かに、SET句の相関サブクエリでマッチングされるレコードが4行なのは事実です。しかし、SET句というのは更新対象を制限しているわけではなく、名前のとおり「SETする」値を決めているだけです。相関サブクエリは、アンマッチな場合にはNULLを返す仕様になっています。そのため、SET句でアンマッチになった「発注明細」テーブルの2レコードには、実はNULLがセットされているのです。変更前も変更後もNULLであるため、一見すると変更されなかったように見えるだけなのです[2]⁠。

WHERE句で更新対象を制限する

UPDATEでもSELECTでも、対象レコードを制限する方法は1つ、WHERE句だ（リスト3⁠）⁠。

UPDATE OrderDetails
   SET (item, quantity)
           = (SELECT item, quantity
                     FROM EntryDetails ED
                    WHERE OrderDetails.entry_id = ED.entry_id
                      AND OrderDetails.entry_seq = ED.entry_seq)
WHERE EXISTS (SELECT *
                 FROM EntryDetails ED
                WHERE OrderDetails.entry_id = ED.entry_id
                  AND OrderDetails.entry_seq = ED.entry_seq);

まったく同じ条件を、SET句でもWHERE句でも書かなければならないんですね。これはKISSの原則に反していませんか。

同じに見えるのは見た目上だけで、機能がまったく違う。前者はマッチングのため、後者は対象レコードを絞り込むためだ。今回はたまたま両者が一致しただけで、両者は異なる条件になることもある。

そっか。ところで更新対象のレコードを制限する理由は、やはり性能ですか？

そうだ。今、WHERE句がない状態では「発注明細」テーブルは常に全件が更新対象になる。これはレコード数が増えれば増えるほど危険だ。更新対象レコードの比率が少ないなら、WHERE句で絞り込むほうが得策だ。

最後に、目下、SQL で更新を行う際に最大の足枷（あしかせ）とも言える難点を見るとしよう。

そこまで言うとはものものしいですね。

まあ見ればわかる。

ちょっとパズル的な問題ですね[3]⁠。

主キーがないなど、少し設定が作為的だが、今はそこは問題としない。これは、更新におけるウィンドウ関数の利点と制限を学べる格好の例題だ。

SET句でウィンドウ関数を使えるか？

ということはウィンドウ関数を使うんですよね。よーし、これでどうだ（リスト4⁠）⁠。

UPDATE Hotel
   SET room_nbr = (floor_nbr * 100)
                    + ROW_NUMBER() OVER (PARTITION BY floor_nbr);

いいな。

いいわね。

……またまた、いつもならここで「ボケ」だの「カス」だの言ってくるところじゃないですか。隠さないでくださいよ。本当はどこが間違っているんですか？

いや、合ってるよ。

合ってるわね。

え……!? え……！ 本当ですか。いやっほう。今日は何だかツイている気がしてきましたよ。

まあ、試験に落ちていればお前の顔を見るのも最後だからな。花を持たせてやらないと。ワハハ。

そ、そういう言い方はないんじゃないかなぁ。

SET句でのウィンドウ関数の威力

ウィンドウ関数のおさらいになりますが、ROW_NUMBERは1から始まる連番を生成する関数です。PARTITION BYは与えられたキーでテーブルをカットします。ここでは、連番のカウンターを1にリセットする区切りとして働いています（要するにフロアが変わるたびに1にリセットしている⁠）⁠。

ウィンドウ関数にORDER BYがないことが気になった人がいるかもしれません。しかし、よく考えてみると今回のケースでは、同じフロア内のレコードであれば、どういう順序で整列してもかまわないので、特に指定する必要はないのです。

残念なお知らせ

またか、と思うかもしれませんが、上記のコードには制限があります。実は、これが動作するのは現在のところDB2だけ（！）です。ほかのDBMSで実行するうえでの障害は、次の2点です[4]⁠。

• ① ウィンドウ関数でORDER BY句を指定しないことが許されない
• ② SET句に直接ウィンドウ関数を記述できない

①の問題は些細（ささい）なことです。ORDER BYは不要なだけで、あって困るものではないので、適当なキーを指定してORDER BY句を追加すればOKです。根本的な制約は②のほうです。実は、DB2 以外のすべてのDBMSでは、ウィンドウ関数を「裸で」記述することが許されていません。SET句でウィンドウ関数を使うには、一度相関サブクエリを間に挟むことによって記述する必要があります。そのためには更新対象のレコードとサブクエリ内のレコードが一対一に対応するように相関サブクエリを作る必要があるのですが、今回のようなケースではそれが不可能です（更新対象のレコードに一意性がないため⁠）⁠。

つまり結論として、ワイリーのすばらしい解答は、どう頑張ってもDB2以外のDBMSには適用できないものなのです。

SET句でウィンドウ関数を使う条件

ワイリーの解は、着眼点は悪くなかったけど、現在のところ実装依存なのが残念ね。

結局ぬかよろこびですよ。

そんな卑下することはないわ。これはむしろ実装の側に責任があるのだから。

SQLがいかに更新機能をないがしろにしているかがよくわかるだろう。さて、それでは汎用的な解法を考えるとしよう。実は、このテーブルの形式である限り、SQLのみで解くことはおそらくできまい。かといって即座にループに頼るのも芸がない。そこで出発点とするテーブルを図10のように変更する。

つまり、客室番号の初期値をNULLではなく一意な連番とするわけ。こうすることで、更新対象のレコードを一意に識別することが可能になるわ[5]⁠。

そうか、これでSET句でウィンドウ関数を使えるようになるのか（リスト5⁠）⁠。

UPDATE Hotel_2
   SET room_nbr
          = (SELECT nbr
               FROM (SELECT room_nbr,
                           (floor_nbr * 100) +
                              ROW_NUMBER() OVER(PARTITION BY floor_nbr
                                           ORDER BY room_nbr) AS nbr
                       FROM Hotel_2) TMP
              WHERE Hotel_2.room_nbr = TMP.nbr);

ほう、今日は冴えているな。連チャンだぞ。

えへへ。ここで1年間しごかれたのは無駄じゃなかったですね。

注5）

レコードの一意識別子としては、OracleのROWID、PostgreSQLのOIDのように、テーブルが保持している擬似ID列を利用する方法もあります。しかしこれは実装依存になるため、本稿では取り上げません。

（PM5:00　休憩室。仕事を終えた3人がくつろいでいる⁠）⁠

いやー今日もいい仕事しましたねー。

今日「も⁠」⁠？

細かいところにこだわらないでくださいよ。いいじゃないですか。

あら、何かしらこの封筒……あら、これワイリーの……。

え、ちょ、ちょっと見せてください。（⁠慌てて封筒を開ける）……あっ、通っている。救命医療の専門コース、通りましたよ！ 先生！

おめでとう、ワイリー。

やれやれ、これでまたお前の馬鹿面を見なきゃならんわけか。

へへへ、ご迷惑おかけします！（ 強引にロバートの腕を引っ張りながら）そうだ、今日は一緒に飲みにいきましょうよ、ね！

いいわねえ。もちろんロバートのおごりでね。

わかった、わかったから袖を引っ張るな。うっとうしい！

ひゃっほう。

（1年後、今年も救命室はロバートとヘレンの二本柱［とワイリー］で運営されている。そして今年も不運なインターンが派遣されてきた……⁠）⁠

……先生、ワイリー先生。

うーんむにゃむにゃ。

先生、起きてください。

うーん、もう。今何時？

夜中の3時です。

5時に起こしてって頼んだでしょ。

急患なんです。

そう……そんな急がなくっても死にはしないって……。よっこらしょと。ロバート先生とヘレン先生は？

別の患者さんを見ています。人手が足りないんです。

そっか……よし、じゃあ君、手伝って。SQL、少しは書けるだろ。

ええっ。ぼ、僕はまだ無理ですよ。学校でもまだ……。

つべこべ言うな。どうせすぐに1人でやらなきゃならないんだ。そら、行くぞ！

【参考資料】

Joe Celko著、ミック訳『SQLパズル 第2版―プログラミングが変わる書き方/考え方』（翔泳社、2007年）

ホテルの部屋番号を更新するパズルは「パズル56　ホテルの部屋番号」を参照。ほかにも更新SQLの練習問題としては、「⁠パズル2　欠勤」「⁠パズル38　記録の更新」をどうぞ。