便利で危険な相関サブクエリ① 更新と組み合わせる

まずは、更新と相関サブクエリを組み合わせるケースを考えます。図1の社員マスタテーブルに加えて、図8のような新しいテーブルを用意します。

部署別の最高齢社員の情報を記録するテーブルです。

まずはそのまま書いてみる

さて、このテーブルはまだ情報が登録されていません。先ほど使った社員テーブル（図1）から、部署別の最高齢の社員と年齢を選択して更新するのが今回の問題です。

すでにレコードが存在するので、更新の道具としては迷わずUPDATE文が選択されるところです。相関サブクエリはUPDATE文の中でも使うことが可能ですので、単純に考えればリスト4のように、先に作った相関サブクエリをそのままSET句の右辺に代入したSQL文ができあがります。

しかしこのUPDATE文はエラーになります。理由は、Oracleでこれを実行したときに出るエラーメッセージが語るとおりです――「単一行副問合せにより2つ以上の行が戻されます⁠」⁠。つまり、1行しか結果が返るべきでない個所で、2行以上の結果が返されている、ということです。それがどこであるか、もうおわかりでしょう。そう、最高齢の社員が2人いる開発部ですね（図9⁠）⁠。

なぜエラーになるのか

リレーショナルデータベースの基本原則の1つに「テーブルに保持されるデータはすべて第1正規形を満たさなければならない」というものがあります。第1正規形とは、言い換えれば「すべてのデータが分割不可能なスカラ値である」ということです。したがって、図10のようなデータの持ち方は、（⁠少なくとも現在の）関係モデルにおいては許されていないのです。

裏を返すと、社員テーブルに小川さんと長谷川さんのどちらか1人だけしか存在しなかった場合には、上のUPDATE文はエラーなく実行できます。したがって、このエラーは、テーブルの状態によって出たり出なかったりする幽霊のように厄介な存在です。ときどき、試験のときにデータのバリエーションが不足していてこのエラーを見過ごし、実運用に入ってから出現することがあるので注意が必要です。

対処法

この幽霊に対処する方法は、大きく2通りあります。

• ① 情報の登録方法をINSERTに変えてしまう
• ② 小川さんと長谷川さんのうち、適当にどちらか1人だけを代表に選んで登録する

どちらを取るのがよいのかは、その場その場の判断です。①INSERTを使った洗い替え方式は、論理的ですっきりしていますが、常に全行DELETE（またはTRUNCATE）して全行INSERTする必要があるので、更新コストが多くかかります。一方、②代表選抜方式は、更新コストは少ないのですが、どういう基準で代表を選ぶかが不明瞭で場当たり的です。

ここでは、①INSERTを使った洗い替え方式のコードを紹介しておきましょう（リスト5⁠）⁠。②代表選抜方式をどうやって実現するかは演習問題としますので、みなさん考えてください（稿末の「演習問題」参照⁠）⁠。

なお、このコードを利用するときは、テーブル定義においても、主キーを（部署）だけから（部署, 社員名）に変更しておく必要がある点を忘れないでください。

まずは全行削除（TRUNCATEでも可）
DELETE FROM EmpMaxAge;

全行登録
INSERT INTO EmpMaxAge
SELECT dept, name, age
  FROM Employees E1
 WHERE age = (SELECT MAX(age)
                FROM Employees E2
               WHERE E1.dept = E2.dept);