[PostgreSQL]SELECTした結果でUPDATEする

この記事は昔SQL Server向けに書いた記事をPostgreSQL向けに書き直したものです。

UPDATE SET FROM の挙動にはクセがある！

PostgreSQLで他のテーブルの値でUPDATEしたい、ってときは、 UPDATE SET FROM 的な構文がよく使われると思います。どういった動作をするのか検証してみました。

お急ぎの方のために結論を先に申し上げておきます。

1. キホンは、JOINしたレコードの値でUPDATE
2. JOINの結果が0件になると、UPDATEされない
3. 複数行JOINできてしまったら、LIMIT 1した結果でUPDATE

です（以前書いたSQL Server向けに書いた記事と同じ結果でした）。それでは以下で詳しく説明していきます。

ここでは、STUDENTが受講したテストのSCOREを記録するというサンプルを考えてみましょう。この学校では、毎日テストが行われ（なんと休日も！）、その結果を日々記録しています。そして、全生徒の「昨日のスコア」が見れてしまうのです。生徒たちは、「あっ、アイツこんな点数取ってる」と、お互い切磋琢磨しています。「昨日のスコア」は、夜間にSCORESテーブルからSTUDENTSテーブルにコピーされ、STUDENTSテーブルは誰でも参照可能になります。

まず、今回使用するデータの準備です。

CREATE TABLE STUDENTS(
    ID INTEGER PRIMARY KEY,
    NAME VARCHAR(32) NOT NULL,
    YESTERDAY_SCORE INTEGER
);
 
CREATE TABLE SCORES(
    STUDENT_ID INTEGER REFERENCES STUDENTS(ID),
    TEST_DATE DATE NOT NULL DEFAULT NOW(),
    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

今日は4月25日月曜日です。まず、今日のテストの結果をINSERTします。

INSERT INTO SCORES VALUES(1, '2016-04-25', 10);
INSERT INTO SCORES VALUES(2, '2016-04-25',  7);
INSERT INTO SCORES VALUES(3, '2016-04-25',  4);

太郎さんはがんばりました。では、この結果をSCORESテーブルからSTUDENTSテーブルにコピーしましょう。

UPDATE STUDENTS
SET YESTERDAY_SCORE = SCORES.SCORE
FROM SCORES
WHERE STUDENTS.ID = SCORES.STUDENT_ID
  AND SCORES.TEST_DATE = '2016-04-25'
;

はい、明快です。JOINに成功したレコードの値でUPDATEされることになります。STUDENTSテーブルは以下のようになるでしょう。

ID	NAME	YESTERDAY_SCORE
1	太郎	10
2	次郎	7
3	三郎	4

わかりやすく、便利な機能ですね。

JOINの結果が0件になると、UPDATEされない

4月26日、今日は三郎さんはお休みでした。しゃあなし2人で受講しました。今日の分をINSERTします。

INSERT INTO SCORES VALUES(1, '2016-04-26',  9);
INSERT INTO SCORES VALUES(2, '2016-04-26',  6);

では、この値をSTUDENTSテーブルにコピーするとどうなるでしょうか、ということがぼくは知りたかったのです。

UPDATE STUDENTS
SET YESTERDAY_SCORE = SCORES.SCORE
FROM SCORES
WHERE STUDENTS.ID = SCORES.STUDENT_ID
  AND SCORES.TEST_DATE = '2016-04-26'
;

と、日付だけを変更して実行しました。すると「2 row(s) affected」と出力され、その結果は以下のようになります。

ID	NAME	YESTERDAY_SCORE
1	太郎	9
2	次郎	6
3	三郎	4

あれ、三郎さんのYESTERDAY_SCOREが昨日のままです。なるほど、SELECTの結果が0行となるときは、UPDATEされずにそのままなのですね。

複数行JOINできてしまったら、LIMIT 1した結果でUPDATE

4月27日、三郎さんはテストを一回お休みできてラッキーと思っていたのもつかの間、先生から2日分のテストをするよう命じられてしまいました。今日の成績をINSERTします。

INSERT INTO SCORES VALUES(1, '2016-04-27',  8);
INSERT INTO SCORES VALUES(2, '2016-04-27',  5);
INSERT INTO SCORES VALUES(3, '2016-04-27',  3);
INSERT INTO SCORES VALUES(3, '2016-04-27',  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 = '2016-04-27'
;

結果は以下のようになりました。

ID	NAME	YESTERDAY_SCORE
1	太郎	8
2	次郎	5
3	三郎	2

今日の日付で2件取得できるはずですが、実際にUPDATEされる値は1レコードのみのようです。複数レコード取得される可能性のある場合、予期せぬ結果となることも考えられます。使いどころをよく考えるべきでしょう。

と、まあ、ここまで調べてきてわかったことですが、UPDATE SET FROMの使い方は便利ですが、副作用もありそうです。気をつけて使うようにしましょう。