行から列への更新
今度は、2つのテーブルを使って、片方の情報をもう一方のテーブルへ編集を加えつつコピーするUPDATE文を考えます。学生のテストの得点を行持ちと列持ちで保持するテーブルをサンプルとします(表3)。
表3サンプルテーブル(行から列へ)
ScoreRows:行持ち
student_id (学生ID) | subject (教科) | score (点数) |
A001 | 英語 | 100 |
A001 | 国語 | 58 |
A001 | 数学 | 90 |
B002 | 英語 | 77 |
B002 | 国語 | 60 |
C001 | 英語 | 52 |
C003 | 国語 | 49 |
C003 | 社会 | 100 |
ScoreCols:列持ち
student_id (学生ID) | score_en (点数- 英) | score_nl (点数- 国) | score_mt (点数- 数) |
A001 | | | |
B002 | | | |
C003 | | | |
D004 | | | |
ここでの問題は、行持ちのテーブルから列持ちのテーブルへ、科目ごとの点数を移すことです。更新後の列持ちテーブルは表4のようなデータ内容となります。
表4 表3のScoreCols更新後
student_id (学生ID) | score_en (点数- 英) | score_nl (点数- 国) | score_mt (点数- 数) |
A001 | 100 | 58 | 90 |
B002 | 77 | 60 | |
C003 | 52 | 49 | |
D004 | | | |
生徒B002とC003の数学の点数は、データソースであるScoreRowsテーブルからはわからないのでNULLのままです。また、そもそもD004という生徒は存在しないので、この生徒については更新対象外とされます。
基本的には、前問と同様にSET句で相関サブクエリを利用します。同じ生徒同士の情報を更新するわけですから、結合キーはstudent_idです。
1つずつ更新する
まず考えつくのは、リスト3のように1教科ずつ更新する「素直な」SQLでしょう。このクエリは非常に明快ですが、3つの相関サブクエリを実行しなければならず、パフォーマンスに問題があります。更新したい教科がもっと増えたらその分だけサブクエリの数も増え、パフォーマンスが悪化するでしょう(それでも、検索条件で主キーのインデックスを使える点が救いですが)。この問題点を改善する方法はないものでしょうか?
リスト化して複数列更新する(Oracle、DB2の場合)
実は、OracleとDB2ならば、リスト4のように複数列をリスト化して一度に更新する方法があります。
こうするとサブクエリが一まとめになり、パフォーマンスも向上してコードもすっきりします。更新列が増えても心配いりません。ここで重要な技術は次の2つです。
① リスト機能
SET句の左辺を見るとわかるように、英・国・数の3つの列を(score_en, score_nl, score_mt)とリスト形式にしています。これによって、リスト全体を1つの操作単位とできるのです。これはSQL-92のリスト比較の応用ですが、SQLServer、PostgreSQL、MySQLではSET句でのリストがサポートされていません[1]。Oracle、DB2でのみ利用可能です。
② スカラ・サブクエリ
サブクエリ内のCASE式によって、教科ごとの点数を取得していますが、重要なのはそれぞれの点数にMAX関数を適用していることです。これは、ScoreRowsテーブルにおいて、ある生徒についての行は複数存在しているので、集約なしではサブクエリが複数行を返すエラーが発生するためです。たとえば生徒「A001」のscore_en 列の場合、MAX関数なしの状態では(100,NULL, NULL)という3行が返されます(2つのNULLは、国語と数学の行についてELSE句でNULLに変換された結果です)。このままではSET句の右辺として使えませんが、MAX関数を適用することでNULLが除外され、「100」という単一の値に変換されます[2]。
NOT NULL制約がついている場合
表3ではScoreColsテーブルの英国数の3列はNULLを許可しています。そのため、ScoreRowsテーブルに存在しない生徒「D004」の全列や「B002」、「C003」の数学列はNULLで更新されることになります。では、もしScoreColsテーブルの全列にNOT NULL制約がついていたらどうでしょう。初期状態はScoreColsNNテーブルのように「0」とします(表5)。
表5 ScoreColsNN:列持ち(NOT NULL 制約つき)
student_id (学生ID) | score_en (点数- 英) | score_nl (点数- 国) | score_mt (点数- 数) |
A001 | 0 | 0 | 0 |
B002 | 0 | 0 | 0 |
C003 | 0 | 0 | 0 |
D004 | 0 | 0 | 0 |
この場合、先ほどの2つのUPDATE文(リスト3、リスト4)を実行すると、どちらもエラーになります。その理由は、結合条件でヒットしなかった教科について、NULLに更新できないからです。これを防ぐには、リスト5、6のようにSQLを修正する必要があります。
これらのコードは、2つのレベルでNULLに対応しています。
まず一つは「そもそもテーブル間で一致しない行が存在した場合」。生徒「D004」が相当します。こういう行はそもそも更新対象から除外する必要があるので、外側のWHERE句のEXISTS述語で「2つのテーブル間で学生IDが一致する行に限る」という条件を追加しています[3]。
もう一つのレベルは、生徒は存在するけれど教科が欠けている場合。いわば「行はあるけど列はない」状態です。「B002」、「C003」の数学がこれに相当します。これは、COALESCE関数でNULLを0に変換することで対応できます。
MERGE文を利用する
実は、このケースに対応するには、もう一つ方法があります。それはリスト7のようにMERGE文を利用するものです。この方法の利点は、UPDATEのときは2ヵ所に分散していた結合条件をON句にまとめてしまえることです。こうすることで、コードを簡潔に保ち将来の変更時に修正ミスをなくせます(2ヵ所に分散していると、片方は直しても、もう一方は直し忘れがちです)。
もともとMERGE文は、UPDATEとINSERTを一度に行うために考案された技術ですが、別にUPDATEだけやINSERTだけ行っても構文上は問題ないという点を利用したトリックです。
パフォーマンスに関して言うと、MERGE文の場合、ScoreRowsテーブルに対するフルスキャン1回+集約1回が必要となります(テーブル同士が1対1に対応するなら、集約は不要ですが)。更新列が増えてもこれは変わらないので、相関サブクエリを複数並べたときのようにパフォーマンスが悪化する危険はありません。あとは、テーブルサイズやインデックスの利用可否などの環境によって、どちらに軍配があがるかは変わってきます。OracleやDB2などMERGE文を使える環境ならば、一つの選択肢として試してみてもよいでしょう。