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

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

サンプル DB の準備

記事内の SQL を試しに実行したい場合は、 sqlite3 、または、 sqlite3.exe を起動して、以下の 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) から利用可能になりました。

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. をカラム名の前につけることで参照できます。

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 制約に引っかかるレコードを既存レコードとしてみなされます。

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 として判断されます。

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 文です。
排他制御の文脈で出てくる「後勝ち」と同様の処理になります。

REPLACE INTO 構文

REPLACE INTO country (colomn_name1, colomn_name2, ...)
VALUES (value1, value2, ...);

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

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 で引っかかる場合もスキップされてしまいます。

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 を使えば解決します。

UPSERT 構文（DO NOTHING 版）

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, column2, ...)
    ON CONFLICT(unique_column1, ...) DO NOTHING
;

続けて実行例です。

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) から利用可能になりました。

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 の構文を真似ています。

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 は以下のように書く必要がありました。

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) から利用可能になりました。

RETURNING 構文

[INSERT Statement | UPDATE Statement | DELETE Statement]
RETURNING
    output_column_name1,
    output_column_name2,
    ...
;

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

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_replace.html
https://www.sqlite.org/lang_insert.html
https://www.sqlite.org/lang_conflict.html
https://www.sqlite.org/lang_update.html#update_from
https://www.sqlite.org/lang_returning.html

SQL:2016 で入っていないことを確認しました。最新版は SQL:2023 ですが、こちらは未確認です。ただ、Wikipedia 見る限り、この記事で紹介する構文はまだ入っていないようです。 ↩