BigQueryのMERGEステートメントの検証

BigQueryのMERGEステートメントはINSERTとUPDATEを同時にできるが、その使い方を理解するために検証してみました。

次の２つのテーブルがあったとします。

#####source_tbl

| id | name | price | update_date |
|:-----------|------------:|:------------:|:------------:|
| 001       | apple        | 150         | 2020/8/4 |
| 002     | orange      | 150       | 2020/8/2 |
| 003       | carrot        | 50         | 2020/8/2 |
| 004         | cabbage          | 100           | 2020/8/3 |
| 005       | pork       | 300       | 2020/8/3 |

#####target_tbl

| id | name | price | update_date |
|:-----------|------------:|:------------:|:------------:|
| 001       | apple        | 100         | 2020/8/1 |
| 002     | orange      | 150       | 2020/8/2 |
| 003       | carrot        | 50         | 2020/8/2 |

* source_tblが更新元テーブル
* target_tblが更新先テーブル
* source_tblとtarget_tblを比較し、source_tblに更新が発生したレコードはtarget_tblへ反映する
* その際、update_dateカラムはSQL実行日に設定する
* SQL実行日は `2020/8/5` とする

##特に条件は指定せず、MERGEをやってみる

```sql
MERGE
    target_tbl t
USING
    source_tbl s
ON
    t.id = s.id
WHEN MATCHED THEN
    UPDATE SET
        t.name = s.name
      , t.price = s.price
      , t.update_date = CURRENT_DATETIME('Asia/Tokyo')
WHEN NOT MATCHED THEN
    INSERT
    (
        id
      , name
      , price
      , update_date
    )
    VALUES
    (
        s.id
      , s.name
      , s.price
      , CURRENT_DATETIME('Asia/Tokyo')
    )
```

###実行結果
#####target_tbl

| id | name | price | update_date |
|:-----------|------------:|:------------:|:------------:|
| 001       | apple        | 150         | 2020/8/5 |
| 002     | orange      | 150       | 2020/8/5 |
| 003       | carrot        | 50         | 2020/8/5 |
| 004         | cabbage          | 100           | 2020/8/5 |
| 005       | pork       | 300       | 2020/8/5 |


全てのレコードが更新されてしまった。


##EXCEPT DISTINCT句を使用し、変更のあったレコードのみ抽出し、それをソーステーブルとしてMERGEしてみる

```sql
MERGE
    target_tbl t
USING
(
    SELECT
        id
      , name
      , price
      , update_date
    FROM source_tbl
    EXCEPT DISTINCT
    SELECT
        id
      , name
      , price
      , update_date
    FROM target_tbl
) s
ON
    t.id = s.id
WHEN MATCHED THEN
    UPDATE SET
        t.name = s.name
      , t.price = s.price
      , t.update_date = CURRENT_DATETIME('Asia/Tokyo')
WHEN NOT MATCHED THEN
    INSERT
    (
        id
      , name
      , price
      , update_date
    )
    VALUES
    (
        id
      , name
      , price
      , CURRENT_DATETIME('Asia/Tokyo')
    )
```

###実行結果
#####target_tbl

| id | name | price | update_date |
|:-----------|------------:|:------------:|:------------:|
| 001       | apple        | 150         | 2020/8/5 |
| 002     | orange      | 150       | 2020/8/2 |
| 003       | carrot        | 50         | 2020/8/2 |
| 004         | cabbage          | 100           | 2020/8/5 |
| 005       | pork       | 300       | 2020/8/5 |

変更があったレコードのみ更新・挿入されている。


##仮に、`更新があったレコードは必ずupdate_dateカラムが更新される` という前提があった場合、EXCEPT DISTINCT句を使用せず、WHEN MATCHED THENにその条件を追加してMERGE

```sql
MERGE
    target_tbl t
USING
    source_tbl s
ON
    t.id = s.id
WHEN MATCHED AND s.update_date != t.update_date THEN
    UPDATE SET
        t.name = s.name
      , t.price = s.price
      , t.update_date = CURRENT_DATETIME('Asia/Tokyo')
WHEN NOT MATCHED THEN
    INSERT
    (
        id
      , name
      , price
      , update_date
    )
    VALUES
    (
        id
      , name
      , price
      , CURRENT_DATETIME('Asia/Tokyo')
    )
```

###実行結果
#####target_tbl

| id | name | price | update_date |
|:-----------|------------:|:------------:|:------------:|
| 001       | apple        | 150         | 2020/8/5 |
| 002     | orange      | 150       | 2020/8/2 |
| 003       | carrot        | 50         | 2020/8/2 |
| 004         | cabbage          | 100           | 2020/8/5 |
| 005       | pork       | 300       | 2020/8/5 |

変更があったレコードのみ更新・挿入されている。
このやり方のほうがシンプル。
ただ、このやり方だとupdate_dateカラム以外が更新され、update_dateカラムは更新されない場合はそのレコードは変更なしと見做され、更新されない。