この記事は昔書いた記事をブログから移行したものです。
UPDATE SET FROM JOIN (WHERE) の挙動にはクセがある！
SQL Serverで他のテーブルの値でUPDATEしたい、ってときは、UPDATE SET FROM JOIN (WHERE)という構文がよく使われると思います。いまぼくが携わっているプロジェクトでもよく使われていたんですが、ぼくにはあまりよくわかりませんでした。と、いうことで、一体どういった動作をするのか検証してみました。
お急ぎの方のために結論を先に申し上げておきます。
- キホンは、JOINしたレコードの値でUPDATE
- JOINの結果が0件になると、UPDATEされない
- 複数行JOINできてしまったら、TOP 1した結果でUPDATE
です。それでは以下で詳しく説明していきます。
ここでは、STUDENTが受講したテストのSCOREを記録するというサンプルを考えてみましょう。この学校では、毎日テストが行われ（なんと休日も！）、その結果を日々記録しています。そして、全生徒の「昨日のスコア」が見れてしまうのです。生徒たちは、「あっ、アイツこんな点数取ってる」と、お互い切磋琢磨しています。「昨日のスコア」は、夜間にSCORESテーブルからSTUDENTSテーブルにコピーされ、STUDENTSテーブルは誰でも参照可能になります。
まず、今回使用するデータの準備です。
CREATE TABLE STUDENTS(
ID INTEGER PRIMARY KEY,
NAME NVARCHAR(32) NOT NULL,
YESTERDAY_SCORE INTEGER
);
CREATE TABLE SCORES(
STUDENT_ID INTEGER REFERENCES STUDENTS(ID),
TEST_DATE DATE NOT NULL DEFAULT GETDATE(),
SCORE INTEGER NOT NULL
);
INSERT INTO STUDENTS VALUES(1, '太郎', NULL);
INSERT INTO STUDENTS VALUES(2, '次郎', NULL);
INSERT INTO STUDENTS VALUES(3, '三郎', NULL);
これで準備ができました。テーブルの中身を確認しておきましょう。
SELECT * FROM STUDENTS;
| ID | NAME | YESTERDAY_SCORE |
|---|---|---|
| 1 | 太郎 | NULL |
| 2 | 次郎 | NULL |
| 3 | 三郎 | NULL |
キホンは、JOINしたレコードの値でUPDATE
今日は10月17日水曜日、大安吉日です。まず、今日のテストの結果をINSERTします。
INSERT INTO SCORES VALUES(1, '2012-10-17', 10);
INSERT INTO SCORES VALUES(2, '2012-10-17', 7);
INSERT INTO SCORES VALUES(3, '2012-10-17', 4);
太郎さんはがんばりました。では、この結果をSCORESテーブルからSTUDENTSテーブルにコピーしましょう。
UPDATE STUDENTS
SET YESTERDAY_SCORE = SCORES.SCORE
FROM STUDENTS
INNER JOIN SCORES
ON STUDENTS.ID = SCORES.STUDENT_ID
AND SCORES.TEST_DATE = '2012-10-17'
;
はい、明快です。JOINに成功したレコードの値でUPDATEされることになります。STUDENTSテーブルは以下のようになるでしょう。
| ID | NAME | YESTERDAY_SCORE |
|---|---|---|
| 1 | 太郎 | 10 |
| 2 | 次郎 | 7 |
| 3 | 三郎 | 4 |
わかりやすく、便利な機能ですね。
JOINの結果が0件になると、UPDATEされない
10月18日、今日は三郎さんはお休みでした。しゃあなし2人で受講しました。今日の分をINSERTします。
INSERT INTO SCORES VALUES(1, '2012-10-18', 9);
INSERT INTO SCORES VALUES(2, '2012-10-18', 6);
では、この値をSTUDENTSテーブルにコピーするとどうなるでしょうか、ということがぼくは知りたかったのです。
UPDATE STUDENTS
SET YESTERDAY_SCORE = SCORES.SCORE
FROM STUDENTS
INNER JOIN SCORES
ON STUDENTS.ID = SCORES.STUDENT_ID
AND SCORES.TEST_DATE = '2012-10-18'
;
と、日付だけを変更して実行しました。すると「2行処理されました」と出力され、その結果は以下のようになります。
| ID | NAME | YESTERDAY_SCORE |
|---|---|---|
| 1 | 太郎 | 9 |
| 2 | 次郎 | 6 |
| 3 | 三郎 | 4 |
あれ、三郎さんのYESTERDAY_SCOREが昨日のままです。なるほど、JOINの結果が0行となるときは、UPDATEされないのですね。これではテストを受けたのかどうかわかりません。ちなみに、
UPDATE STUDENTS
SET YESTERDAY_SCORE = SCORES.SCORE
FROM STUDENTS
INNER JOIN SCORES
ON STUDENTS.ID = SCORES.STUDENT_ID
AND SCORES.TEST_DATE = '2012-10-18'
WHERE STUDENTS.ID = 3
;
のようにWHERE句をつけたところで、「0行処理されました」となり、やっぱりUPDATEされません。では、先ほどのSQLを以下のようにするとどうでしょうか。
UPDATE STUDENTS
SET YESTERDAY_SCORE = SCORES.SCORE
FROM STUDENTS
LEFT OUTER JOIN SCORES -- INNER JOIN から LEFT OUTER JOIN に変更
ON STUDENTS.ID = SCORES.STUDENT_ID
AND SCORES.TEST_DATE = '2012-10-18'
;
「3行処理されました」だそうです。よし。つまり、INNER JOINの結果では0行ですが、LEFT JOINとした場合にはNULLを含めた行が返ります。従って、実行後は以下のようになります。
| ID | NAME | YESTERDAY_SCORE |
|---|---|---|
| 1 | 太郎 | 9 |
| 2 | 次郎 | 6 |
| 3 | 三郎 | NULL |
これでテストを受けていないとわかりますね。
複数行JOINできてしまったら、TOP 1した結果でUPDATE
10月19日、三郎さんはテストを一回お休みできてラッキーと思っていたのもつかの間、先生から2日分のテストをするよう命じられてしまいました。今日の成績をINSERTします。
INSERT INTO SCORES VALUES(1, '2012-10-19', 8);
INSERT INTO SCORES VALUES(2, '2012-10-19', 5);
INSERT INTO SCORES VALUES(3, '2012-10-19', 3);
INSERT INTO SCORES VALUES(3, '2012-10-19', 2);
結果は散々でした。今日はこれまでと同じコピーの処理でどうなるのでしょうか。昨日の反省を踏まえ、LEFT OUTER JOINとし、日付を変えて実行してみます。
UPDATE STUDENTS
SET YESTERDAY_SCORE = SCORES.SCORE
FROM STUDENTS
LEFT OUTER JOIN SCORES
ON STUDENTS.ID = SCORES.STUDENT_ID
AND SCORES.TEST_DATE = '2012-10-19'
;
結果は以下のようになりました。
| ID | NAME | YESTERDAY_SCORE |
|---|---|---|
| 1 | 太郎 | 8 |
| 2 | 次郎 | 5 |
| 3 | 三郎 | 3 |
今日の日付で2件取得できるはずですが、実際にUPDATEされる値は1レコードのみのようです。複数レコード取得される可能性のある場合、予期せぬ結果となることも考えられます。使いどころをよく考えるべきでしょう。
と、まあ、ここまで調べてきてわかったことですが、UPDATE SET FROM JOINは便利ですが、気をつけて使うようにしましょう。