Posted at

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

More than 3 years have passed since last 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の使い方は便利ですが、副作用もありそうです。気をつけて使うようにしましょう。