LoginSignup
2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

コンピューターシステム株式会社Advent Calendar 2024

Day 2
@nimzo6689inコンピューターシステム株式会社

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

Last updated at Posted at 2024-12-01

標準 SQL1 に入っていないので、 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

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

2
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?