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


この記事は昔[SQL Server向けに書いた記事](http://qiita.com/youcune/items/b1b66a709ceb7baae5fe)をPostgreSQL向けに書き直したものです。

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

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

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

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

です（以前書いた[SQL Server向けに書いた記事](http://qiita.com/youcune/items/b1b66a709ceb7baae5fe)と同じ結果でした）。それでは以下で詳しく説明していきます。

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

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

```sql  
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);
```

これで準備ができました。テーブルの中身を確認しておきましょう。

```sql  
SELECT * FROM STUDENTS;
```

| ID | NAME | YESTERDAY_SCORE |
|:--:|:----:|----------------:|
|1|太郎|*NULL*|
|2|次郎|*NULL*|
|3|三郎|*NULL*|

## キホンは、JOINしたレコードの値でUPDATE

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

```sql  
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テーブルにコピーしましょう。

```sql  
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します。

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

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

```sql  
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します。

```sql
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とし、日付を変えて実行してみます。

```sql  
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の使い方は便利ですが、副作用もありそうです。気をつけて使うようにしましょう。