行から列への更新
今度は、
ScoreRows:行持ち
student_ | subject | score |
---|---|---|
A001 | 英語 | 100 |
A001 | 国語 | 58 |
A001 | 数学 | 90 |
B002 | 英語 | 77 |
B002 | 国語 | 60 |
C001 | 英語 | 52 |
C003 | 国語 | 49 |
C003 | 社会 | 100 |
student_ | score_ | score_ | score_ |
---|---|---|---|
A001 | |||
B002 | |||
C003 | |||
D004 |
ここでの問題は、
student_ | score_ | score_ | score_ |
---|---|---|---|
A001 | 100 | 58 | 90 |
B002 | 77 | 60 | |
C003 | 52 | 49 | |
D004 |
生徒B002とC003の数学の点数は、
基本的には、
1つずつ更新する
まず考えつくのは、
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の場合)
実は、
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式によって、
NOT NULL制約がついている場合
表3ではScoreColsテーブルの英国数の3列はNULLを許可しています。そのため、
student_ | score_ | score_ | score_ |
---|---|---|---|
A001 | 0 | 0 | 0 |
B002 | 0 | 0 | 0 |
C003 | 0 | 0 | 0 |
D004 | 0 | 0 | 0 |
この場合、
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対応
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対応
これらのコードは、
まず一つは
もう一つのレベルは、
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文は、
パフォーマンスに関して言うと、