標準 SQL 以外で使用機会ありそうな SQLite の更新系の構文 5 選

標準 SQL[^1] に入っていないので、 SQL の入門書や IPA の試験などには出てきませんが、
実務では意外と使いどころありそうな更新系の SQL をまとめてみました。

[^1]: SQL:2016 で入っていないことを確認しました。最新版は SQL:2023 ですが、こちらは未確認です。ただ、[Wikipedia](https://en.wikipedia.org/wiki/SQL:2023) 見る限り、この記事で紹介する構文はまだ入っていないようです。

SQLite で紹介していますが、他の RDB でも同じような機能がサポートされていたりします。
ただ、いずれも標準規格ではない構文のため、書き方や挙動は RDB によってやや違っています。

# サンプル DB の準備

記事内の SQL を試しに実行したい場合は、 `sqlite3` 、または、 `sqlite3.exe` を起動して、以下の SQL を実行してください。

```sql
-- スキーマ
CREATE TABLE country (
  country_id INTEGER NOT NULL,
  country_name TEXT NOT NULL,
  created_at TEXT DEFAULT (DATETIME('NOW')),
  updated_at TEXT DEFAULT (DATETIME('NOW')),
  PRIMARY KEY (country_id)
);

CREATE TRIGGER country_trigger_au AFTER UPDATE ON country
 BEGIN
  UPDATE country SET updated_at = DATETIME('NOW')
   WHERE rowid = new.rowid;
 END
;

.header on
.mode table
```

# データがなければ挿入し、あれば更新する | UPSERT

UPSERT とは、 DB などに対する操作種別の一つで、指定のデータがなければ挿入し、あれば更新することを意味する一般的な IT 用語です。

SQLite では、UPSERT 文という形で提供されているわけではなく、
INSERT 文の拡張（`ON CONFLICT` 句）で実現できるようになっています。

バージョン 3.24.0 (2018-06-04) から利用可能になりました。

```sql:UPSERT 構文
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, column2, ...)
    ON CONFLICT(unique_column1, ...) DO UPDATE SET 
      target_column1 = 'value',
      target_column2 = excluded.column2
 [WHERE filter_column = xxxx]
;
```

`DO UPDATE` 句で更新する際に、`VALUES` 句で指定している値を参照したいときは `excluded.` をカラム名の前につけることで参照できます。

```sql:UPSERT 実行例
-- country_id=1 は登録されていないので、 INSERT される。
INSERT INTO country (country_id, country_name)
VALUES (1, 'Nippon')
    ON CONFLICT(country_id) DO UPDATE SET 
      country_name = excluded.country_name
;

-- 国名が Nippon として登録される。
SELECT * FROM country;
-- +------------+--------------+---------------------+---------------------+
-- | country_id | country_name |     created_at      |     updated_at      |
-- +------------+--------------+---------------------+---------------------+
-- | 1          | Nippon       | 2024-10-19 04:42:27 | 2024-10-19 04:42:27 |
-- +------------+--------------+---------------------+---------------------+

-- country_id=1 は既に登録されているはずなので、そのレコードが更新される。
INSERT INTO country (country_id, country_name)
VALUES (1, 'Japan')
    ON CONFLICT(country_id) DO UPDATE SET 
      country_name = excluded.country_name
;

-- 国名が Nippon から Japan に更新されている。
SELECT * FROM country;
-- +------------+--------------+---------------------+---------------------+
-- | country_id | country_name |     created_at      |     updated_at      |
-- +------------+--------------+---------------------+---------------------+
-- | 1          | Japan        | 2024-10-19 04:42:27 | 2024-10-19 04:42:51 |
-- +------------+--------------+---------------------+---------------------+
```

## 補足 1 ｜ CONFLICT に指定するカラム

`ON CONFLICT` 句に列が未指定の場合、PK or UK 制約に引っかかるレコードを既存レコードとしてみなされます。

```sql:UPSERT 実行例
-- country_id=1 は既に登録されているはずなので、そのレコードが更新される。
INSERT INTO country (country_id, country_name)
VALUES (1, '日本')
    ON CONFLICT DO UPDATE SET 
      country_name = excluded.country_name
;

-- 国名が Japan から 日本 に更新されている。
SELECT * FROM country;
-- +------------+--------------+---------------------+---------------------+
-- | country_id | country_name |     created_at      |     updated_at      |
-- +------------+--------------+---------------------+---------------------+
-- | 1          | 日本           | 2024-10-19 04:42:27 | 2024-10-19 04:43:43 |
-- +------------+--------------+---------------------+---------------------+
```

## 補足 2 ｜ INSERT INTO ~ SELECT を実行する場合

FROM 句の次に ON 句が来た場合、テーブル結合条件の ON なのか、 CONFLICT 句の ON なのか曖昧になってしまうため、その場合は WHERE true と書けば CONFLICT 句の ON として判断されます。

```sql:UPSERT 実行例
-- country_id=1 は既に登録されているはずなので、そのレコードが更新される。
INSERT INTO country (country_id, country_name)
SELECT country_id, country_name FROM country WHERE true
    ON CONFLICT DO UPDATE SET 
      country_name = 'Japan'
;

-- 国名が 日本 から Japan に更新されている。
SELECT * FROM country;
-- +------------+--------------+---------------------+---------------------+
-- | country_id | country_name |     created_at      |     updated_at      |
-- +------------+--------------+---------------------+---------------------+
-- | 1          | Japan        | 2024-10-19 04:42:27 | 2024-10-19 04:44:46 |
-- +------------+--------------+---------------------+---------------------+
```

# 既存レコードを置き換える | 後勝ち

通常の INSERT 文では、UK 制約に引っかかるレコードを挿入しようとした場合、
エラー（厳密にいえば ABORT でトランザクションが生きたまま処理は中断される）になります。

`REPLACE INTO` 文はそれをエラーとせず、既存レコードを削除して新しいレコードを挿入するという SQL 文です。
排他制御の文脈で出てくる「後勝ち」と同様の処理になります。

```sql:REPLACE INTO 構文
REPLACE INTO country (colomn_name1, colomn_name2, ...)
VALUES (value1, value2, ...);
```

REPLACE INTO は他の RDB との互換性を意識して作られたエイリアス記法で、
他にも `INSERT OR REPLACE INTO` または、 `INSERT ON CONFLICT REPLACE INTO` と書けます。

```sql:REPLACE INTO 実行例
SELECT * FROM country;
-- +------------+--------------+---------------------+---------------------+
-- | country_id | country_name |     created_at      |     updated_at      |
-- +------------+--------------+---------------------+---------------------+
-- | 1          | Japan        | 2024-10-19 04:42:27 | 2024-10-19 04:44:46 |
-- +------------+--------------+---------------------+---------------------+

REPLACE INTO country (country_id, country_name) VALUES (1, 'Nippon');

SELECT * FROM country;
-- +------------+--------------+---------------------+---------------------+
-- | country_id | country_name |     created_at      |     updated_at      |
-- +------------+--------------+---------------------+---------------------+
-- | 1          | Nippon       | 2024-10-19 04:47:52 | 2024-10-19 04:47:52 |
-- +------------+--------------+---------------------+---------------------+
```

# 既にレコードがあれば INSERT 処理をスキップする | 先勝ち

`REPLACE INTO` は後勝ちでしたが、これを逆に「先勝ち」にしたいケースがあります。
Google 検索で調べると、 `INSERT OR IGNORE INTO` が紹介されていたりしますが、先勝ちの目的でこれを使用するのは誤用です。

確かに、 `IGNORE` を付けると、UK 制約で引っかかったレコードは挿入をスキップされますが、
それ以外にも、 `NOT NULL` や `CHECK` で引っかかる場合もスキップされてしまいます。

```sql:INSERT OR IGNORE INTO 構文（非推奨）
-- 国名(country_name)は NOT NULL 制約があるが、エラーにならずにスキップされてしまう。
INSERT OR IGNORE INTO country (country_id, country_name) VALUES (2, NULL);

-- country_id=2 のレコードは登録されていない。
SELECT * FROM country;
```

ではどうすればよいか。
既に紹介している `UPSERT` の構文にある `DO NOTHING` を使えば解決します。

```sql:UPSERT 構文（DO NOTHING 版）
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, column2, ...)
    ON CONFLICT(unique_column1, ...) DO NOTHING
;
```

続けて実行例です。

```sql:UPSERT 実行例（DO NOTHING 版）
SELECT * FROM country;
-- +------------+--------------+---------------------+---------------------+
-- | country_id | country_name |     created_at      |     updated_at      |
-- +------------+--------------+---------------------+---------------------+
-- | 1          | Nippon       | 2024-10-19 04:47:52 | 2024-10-19 04:47:52 |
-- +------------+--------------+---------------------+---------------------+

INSERT INTO country (country_id, country_name)
VALUES (1, 'Japan')
    ON CONFLICT DO NOTHING
;

-- レコードも挿入されず、 update_at も更新されていない。
SELECT * FROM country;
-- +------------+--------------+---------------------+---------------------+
-- | country_id | country_name |     created_at      |     updated_at      |
-- +------------+--------------+---------------------+---------------------+
-- | 1          | Nippon       | 2024-10-19 04:47:52 | 2024-10-19 04:47:52 |
-- +------------+--------------+---------------------+---------------------+
```

UPSERT 便利ですね。

# UPDATE のサブクエリが省略できる | UPDATE ~ FROM

UPDATE 文に他のテーブルの値を使って更新する行を選択したり、更新に用いる値を使ったりする際に、 `UPDATE-FROM` を使うと簡潔に書けます。
バージョン 3.33.0 (2020-08-14) から利用可能になりました。

```sql:UPDATE ~ FROM 構文
UPDATE target_table AS t SET
       column_name1 = 'value', ...
  FROM source_table AS s
 WHERE t.something_id = s.something_id
   AND s.filter_column = 'value'
;
```

`UPDATE-FROM` は標準 SQL には入っていないため、 SQLite では他の構文と同様 PostgreSQL の構文を真似ています。

```sql:UPDATE ~ FROM 実行例
CREATE TABLE IF NOT EXISTS new_country AS SELECT * FROM country;
UPDATE country SET country_name = 'Japan(Update-From)' WHERE country_id = 1;

SELECT * FROM new_country;
-- +------------+--------------+---------------------+---------------------+
-- | country_id | country_name |     created_at      |     updated_at      |
-- +------------+--------------+---------------------+---------------------+
-- | 1          | Nippon       | 2024-10-19 04:47:52 | 2024-10-19 04:47:52 |
-- +------------+--------------+---------------------+---------------------+

UPDATE new_country AS new SET
       country_name = old.country_name 
  FROM country AS old
 WHERE new.country_id = old.country_id
;

SELECT * FROM new_country;
-- +------------+--------------------+---------------------+---------------------+
-- | country_id |    country_name    |     created_at      |     updated_at      |
-- +------------+--------------------+---------------------+---------------------+
-- | 1          | Japan(Update-From) | 2024-10-19 04:47:52 | 2024-10-19 04:47:52 |
-- +------------+--------------------+---------------------+---------------------+
```

ちなみにこの構文がサポートされる以前は、上のような SQL は以下のように書く必要がありました。

```sql:UPDATE ~ FROM 構文（未使用版）
UPDATE new_country
SET country_name = (
    SELECT old.country_name
    FROM country AS old
    WHERE new_country.country_id = old.country_id
)
WHERE EXISTS (
    SELECT 'X'
    FROM country AS old
    WHERE new_country.country_id = old.country_id
);
```

この書き方の場合、`new_country` にエイリアスが付けられないという制約をあり、
冗長の上にちょっと罠仕様感がありました。

# 更新された行のデータを返す | RETURNING

登録、更新後に `AUTO INCREMENT` された値や `DEFAULT` 句で生成される値（`DATETIME('NOW')` など）を取得したい場合、 `RETURNING` 句を後ろに追加することでそれが実現できます。
バージョン 3.35.0 (2021-03-12) から利用可能になりました。

```sql:RETURNING 構文
[INSERT Statement | UPDATE Statement | DELETE Statement]
RETURNING
    output_column_name1,
    output_column_name2,
    ...
;
```

SQL の結果をログで出力したい時などに便利です。
PostgreSQL など、他の DB ではストアドで利用すると、更新後の SELECT 文は省略できる場面もあります。

```sql:RETURNING 実行例
DELETE FROM country
RETURNING
    country_id, country_name
;
-- +------------+--------------------+
-- | country_id |    country_name    |
-- +------------+--------------------+
-- | 1          | Japan(Update-From) |
-- +------------+--------------------+

-- RETURNING 句でも * で全カラム指定が可能です。
INSERT INTO country (country_id, country_name)
VALUES (1, 'Japan')
RETURNING
    *
;
-- +------------+--------------+---------------------+---------------------+
-- | country_id | country_name |     created_at      |     updated_at      |
-- +------------+--------------+---------------------+---------------------+
-- | 1          | Japan        | 2024-10-19 05:07:26 | 2024-10-19 05:07:26 |
-- +------------+--------------+---------------------+---------------------+

UPDATE country SET
    country_name = 'Nippon'
 WHERE country_id = 1
RETURNING
    *
;
-- +------------+--------------+---------------------+---------------------+
-- | country_id | country_name |     created_at      |     updated_at      |
-- +------------+--------------+---------------------+---------------------+
-- | 1          | Nippon       | 2024-10-19 05:07:26 | 2024-10-19 05:07:26 |
-- +------------+--------------+---------------------+---------------------+
```

実行例の最後の `UPDATE` 文の結果の通り、 `RETURNING` はトリガーで更新される前の値が返ってきてしまうようです。
（そこは後の値が返ってきてほしかった。）

# 参考

[https://www.sqlite.org/lang_upsert.html](https://www.sqlite.org/lang_upsert.html)
[https://www.sqlite.org/lang_replace.html](https://www.sqlite.org/lang_replace.html)
[https://www.sqlite.org/lang_insert.html](https://www.sqlite.org/lang_insert.html)
[https://www.sqlite.org/lang_conflict.html](https://www.sqlite.org/lang_conflict.html)
[https://www.sqlite.org/lang_update.html#update_from](https://www.sqlite.org/lang_update.html#update_from)
[https://www.sqlite.org/lang_returning.html](https://www.sqlite.org/lang_returning.html)