SQLアタマアカデミー
第2回 更新なんてこわくない! (2)行から列への更新
行から列への更新
今度は,
表3サンプルテーブル
ScoreRows:行持ち
student_ | subject | score |
---|---|---|
A001 | 英語 | 100 |
A001 | 国語 | 58 |
A001 | 数学 | 90 |
B002 | 英語 | 77 |
B002 | 国語 | 60 |
C001 | 英語 | 52 |
C003 | 国語 | 49 |
C003 | 社会 | 100 |
ScoreCols:列持ち
student_ | score_ | score_ | score_ |
---|---|---|---|
A001 | |||
B002 | |||
C003 | |||
D004 |
ここでの問題は,
表4 表3のScoreCols更新後
student_ | score_ | score_ | score_ |
---|---|---|---|
A001 | 100 | 58 | 90 |
B002 | 77 | 60 | |
C003 | 52 | 49 | |
D004 |
生徒B002とC003の数学の点数は,
基本的には,
1つずつ更新する
まず考えつくのは,
リスト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の場合)
実は,
リスト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);
こうするとサブクエリが一まとめになり,
① リスト機能
SET句の左辺を見るとわかるように,
② スカラ・サブクエリ
サブクエリ内のCASE式によって,
- 注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を許可しています。そのため,
表5 ScoreColsNN:列持ち
student_ | score_ | score_ | score_ |
---|---|---|---|
A001 | 0 | 0 | 0 |
B002 | 0 | 0 | 0 |
C003 | 0 | 0 | 0 |
D004 | 0 | 0 | 0 |
この場合,
リスト5 リスト3
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
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対応
これらのコードは,
まず一つは
もう一つのレベルは,
- 注3)
- Oracleだけは気を利かして,
EXISTS述語の条件を記述しなくても正常に更新を行えるのですが, 一般性のない独自拡張ですので頼らないほうがいいでしょう。
MERGE文を利用する
実は,
リスト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文は,
パフォーマンスに関して言うと,
SQL.データベース, 更新, NULL, リスト化, MERGE文
バックナンバー
SQLアタマアカデミー
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (5)集合指向と手続き型
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (4)OLAP関数と集約関数を組み合わせる
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (3)OLAP専用関数
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (2)OLAP関数の基本構文
- 最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (1)OLAP関数とは何か
- 第10回 結合大全 (5)非等値結合
- 第10回 結合大全 (4)自己結合
- 第10回 結合大全 (3)外部結合
- 第10回 結合大全 (2)内部結合
- 第10回 結合大全 (1)クロス結合