SQLアタマアカデミー

第2回　更新なんてこわくない！　（2）行から列への更新

初出：WEB+DB PRESS Vol.46（2008年8月24日発売）

SQL.データベース, 更新, NULL, リスト化, MERGE文

この記事を読むのに必要な時間：およそ 4 分

行から列への更新

今度は，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
（点数- 数）
A0011005890
B0027760　
C0035249　
D004　　　

生徒B002とC003の数学の点数は，データソースであるScoreRowsテーブルからはわからないのでNULLのままです。また，そもそもD004という生徒は存在しないので，この生徒については更新対象外とされます。

基本的には，前問と同様にSET句で相関サブクエリを利用します。同じ生徒同士の情報を更新するわけですから，結合キーはstudent_idです。

1つずつ更新する

まず考えつくのは，リスト3のように1教科ずつ更新する「素直な」SQLでしょう。このクエリは非常に明快ですが，3つの相関サブクエリを実行しなければならず，パフォーマンスに問題があります。更新したい教科がもっと増えたらその分だけサブクエリの数も増え，パフォーマンスが悪化するでしょう（それでも，検索条件で主キーのインデックスを使える点が救いですが⁠）⁠。この問題点を改善する方法はないものでしょうか？

リスト3　行→列の更新SQL：素直だけど非効率

UPDATE ScoreCols
   SET score_en = (SELECT score
                     FROM ScoreRows SR
                     WHERE SR.student_id = ScoreCols.student_id
                      AND subject = '英語'),
       score_nl = (SELECT score
                     FROM ScoreRows SR
                     WHERE SR.student_id = ScoreCols.student_id
                      AND subject = '国語'),
       score_mt = (SELECT score
                     FROM ScoreRows SR
                     WHERE SR.student_id = ScoreCols.student_id
                      AND subject = '数学');

リスト化して複数列更新する（Oracle，DB2の場合）

実は，OracleとDB2ならば，リスト4のように複数列をリスト化して一度に更新する方法があります。

リスト4　より効率的なSQL：リスト機能の利用

UPDATE ScoreCols
   SET (score_en, score_nl, score_mt) ―複数列をリスト化して一度で更新
         = (SELECT MAX(CASE WHEN subject = '英語'
                            THEN score
                            ELSE NULL END) AS score_en,
                   MAX(CASE WHEN subject = '国語'
                            THEN score
                            ELSE NULL END) AS score_nl,
                   MAX(CASE WHEN subject = '数学'
                            THEN score
                            ELSE NULL END) AS score_mt
              FROM ScoreRows SR
              WHERE SR.student_id = ScoreCols.student_id);

こうするとサブクエリが一まとめになり，パフォーマンスも向上してコードもすっきりします。更新列が増えても心配いりません。ここで重要な技術は次の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⁠）⁠。

注1）
正確には，PostgreSQL の場合，SET (score_en, score_nl,score_mt) = (1,1,1)のように単純なスカラ値を右辺に使うことは可能ですが，サブクエリを使用できません。
注2）
gihyo.jp連載「SQLアタマ養成講座」第8回でも，MAX関数のこの特性を利用して複数行を1行に集約するテクニックを紹介しています。

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
（点数- 数）
A001000
B002000
C003000
D004000

この場合，先ほどの2つのUPDATE文（リスト3，リスト4）を実行すると，どちらもエラーになります。その理由は，結合条件でヒットしなかった教科について，NULLに更新できないからです。これを防ぐには，リスト5，6のようにSQLを修正する必要があります。

リスト5　リスト3（1列ずつ更新）のNOT NULL制約対応

UPDATE ScoreColsNN
   SET score_en = COALESCE((SELECT score ―生徒は存在するが教科が存在しなかった場合のNULL対応
                              FROM ScoreRows
                             WHERE student_id = ScoreColsNN.student_id
                              AND subject = '英語'), 0),
       score_nl = COALESCE((SELECT score
                              FROM ScoreRows
                             WHERE student_id = ScoreColsNN.student_id
                              AND subject = '国語'), 0),
       score_mt = COALESCE((SELECT score
                              FROM ScoreRows
                             WHERE student_id = ScoreColsNN.student_id
                              AND subject = '数学'), 0)
WHERE EXISTS (SELECT * ――――――――――――――――――――――――――――――――――――――┐
                FROM ScoreRows                               ｜
                WHERE student_id = ScoreColsNN.student_id);  ┘そもそも生徒が存在しなかった場合のNULL対応

リスト6　リスト4（リスト機能の利用）のNOT NULL制約対応

UPDATE ScoreColsNN
   SET (score_en, score_nl, score_mt)
         = (SELECT COALESCE(MAX(CASE WHEN subject = '英語' ―生徒は存在するが教科が存在しなかった場合のNULL対応
                                     THEN score
                                     ELSE NULL END), 0) AS score_en,
                   COALESCE(MAX(CASE WHEN subject = '国語'
                                     THEN score
                                     ELSE NULL END), 0) AS score_nl,
                   COALESCE(MAX(CASE WHEN subject = '数学'
                                     THEN score
                                     ELSE NULL END), 0) AS score_mt
               FROM ScoreRows SR
              WHERE SR.student_id = ScoreColsNN.student_id)
 WHERE EXISTS (SELECT * ――――――――――――――――――――――――――――――――――――――┐
                 FROM ScoreRows                               ｜
                 WHERE student_id = ScoreColsNN.student_id);  ┘そもそも生徒が存在しなかった場合のNULL対応

これらのコードは，2つのレベルでNULLに対応しています。

まず一つは「そもそもテーブル間で一致しない行が存在した場合⁠」⁠。生徒「D004」が相当します。こういう行はそもそも更新対象から除外する必要があるので，外側のWHERE句のEXISTS述語で「2つのテーブル間で学生IDが一致する行に限る」という条件を追加しています（注3⁠）⁠。

もう一つのレベルは，生徒は存在するけれど教科が欠けている場合。いわば「行はあるけど列はない」状態です。「⁠B002⁠」⁠，「⁠C003」の数学がこれに相当します。これは，COALESCE関数でNULLを0に変換することで対応できます。

注3）
Oracleだけは気を利かして，EXISTS述語の条件を記述しなくても正常に更新を行えるのですが，一般性のない独自拡張ですので頼らないほうがいいでしょう。

MERGE文を利用する

実は，このケースに対応するには，もう一つ方法があります。それはリスト7のようにMERGE文を利用するものです。この方法の利点は，UPDATEのときは2ヵ所に分散していた結合条件をON句にまとめてしまえることです。こうすることで，コードを簡潔に保ち将来の変更時に修正ミスをなくせます（2ヵ所に分散していると，片方は直しても，もう一方は直し忘れがちです⁠）⁠。

リスト7　MERGE文を利用して複数列を更新

MERGE INTO ScoreColsNN
   USING (SELECT student_id,
                 COALESCE(MAX(CASE WHEN subject = '英語'
                                   THEN score
                                   ELSE NULL END), 0) AS score_en,
                 COALESCE(MAX(CASE WHEN subject = '国語'
                                   THEN score
                                   ELSE NULL END), 0) AS score_nl,
                 COALESCE(MAX(CASE WHEN subject = '数学'
                                   THEN score
                                   ELSE NULL END), 0) AS score_mt
            FROM ScoreRows
           GROUP BY student_id) SR
      ON (ScoreColsNN.student_id = SR.student_id) ―結合条件を1ヵ所にまとめられる
   WHEN MATCHED THEN
        UPDATE SET ScoreColsNN.score_en = SR.score_en,
                   ScoreColsNN.score_nl = SR.score_nl,
                   ScoreColsNN.score_mt = SR.score_mt;

もともとMERGE文は，UPDATEとINSERTを一度に行うために考案された技術ですが，別にUPDATEだけやINSERTだけ行っても構文上は問題ないという点を利用したトリックです。

パフォーマンスに関して言うと，MERGE文の場合，ScoreRowsテーブルに対するフルスキャン1回＋集約1回が必要となります（テーブル同士が1対1に対応するなら，集約は不要ですが⁠）⁠。更新列が増えてもこれは変わらないので，相関サブクエリを複数並べたときのようにパフォーマンスが悪化する危険はありません。あとは，テーブルサイズやインデックスの利用可否などの環境によって，どちらに軍配があがるかは変わってきます。OracleやDB2などMERGE文を使える環境ならば，一つの選択肢として試してみてもよいでしょう。

SQL.データベース, 更新, NULL, リスト化, MERGE文

著者プロフィール

ミック

SI企業に勤務するDBエンジニア。主にデータウェアハウス業務に従事している。自身のサイト「リレーショナル・データベースの世界」でデータベースとSQLについての技術情報を公開している。『Web+DB Press』で「SQLアタマアカデミー」を連載中。

著書：『達人に学ぶ SQL徹底指南書』（翔泳社、2008）訳書：J.セルコ『SQLパズル 第2版』（翔泳社、2007）

SQLアタマアカデミー：サポートページ