SQLアタマアカデミー

第9回SQLでループ! 相関サブクエリの使い方~切れ過ぎるナイフにご用心~ (3)便利で危険な相関サブクエリ①

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

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

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

図8 部署別の最高齢社員の情報を記録するテーブルを作成

EmpMaxAge

dept
(部署)
name
(社員名)
age
(年齢)
営業  
総務  
開発  

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

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

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

リスト4 部署ごとの最高齢社員を更新する(エラー!)

画像

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

図9 エラーになっているテーブルのイメージ

EmpMaxAge

画像

なぜエラーになるのか

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

図10 このようなテーブルは存在しえない
dept
(部署)
name
(社員名)
age
(年齢)
営業石川40
総務藤田50
開発小川
長谷川
29
29

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

対処法

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

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

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

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

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

リスト5 ① INSERTを使った洗い替え方式
まずは全行削除(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);

おすすめ記事

記事・ニュース一覧