SQLiteのUPSERTとREPLACEの違いを確かめる

同じIDの行を入れ直す処理では、UPSERTとINSERT OR REPLACEを同じ更新と考えないようにします。どちらも重複エラーを避ける場面で見かけますが、REPLACEには衝突した既存行を削除する動作があります。

その行をほかのテーブルが参照していると、削除時の条件も関係します。値が最後に同じように見えるだけで、周辺のデータへ同じ影響になるとは限りません。

子のメモを持つ架空DBで比較

次の例は取引先Aを参照するメモを一件持つメモリーDBです。親が削除されたらメモも削除するON DELETE CASCADEを、違いを確認するために明示しています。利用者のDBへは接続しません。

import sqlite3

db = sqlite3.connect(":memory:")
try:
    db.execute("PRAGMA foreign_keys=ON")
    assert db.execute("PRAGMA foreign_keys").fetchone() == (1,)
    db.executescript(
        "CREATE TABLE clients(id TEXT PRIMARY KEY NOT NULL,name TEXT);"
        "CREATE TABLE notes(id TEXT PRIMARY KEY NOT NULL,"
        "client_id TEXT REFERENCES clients(id) ON DELETE CASCADE);"
    )
    with db:
        db.execute("INSERT INTO clients VALUES (?,?)", ("A", "旧名"))
        db.execute("INSERT INTO notes VALUES (?,?)", ("N1", "A"))
    with db:
        db.execute(
            "INSERT INTO clients(id,name) VALUES (?,?) "
            "ON CONFLICT(id) DO UPDATE SET name=excluded.name",
            ("A", "新名"),
        )
    upsert_count = db.execute("SELECT count(*) FROM notes").fetchone()[0]
    print("UPSERT後のメモ件数:", upsert_count)
    with db:
        db.execute("INSERT OR REPLACE INTO clients VALUES (?,?)", ("A", "再登録"))
    replace_count = db.execute("SELECT count(*) FROM notes").fetchone()[0]
    print("REPLACE後のメモ件数:", replace_count)
    assert upsert_count == 1 and replace_count == 0
finally:
    db.close()

UPSERT後のメモは一件、REPLACE後はゼロ件です。Windows・Python 3.12.10・SQLite 3.49.1で、この架空DBの動作を確認しました。REPLACEが親の削除を伴い、設定した連動削除が子へ及ぶ例です。

すべてのDBでメモが消えるという意味ではありません。外部キー、削除時の動作、トリガーなど、実際の定義によって影響を確認する必要があります。

更新する列を指定する

UPSERTのON CONFLICT(id)は、どの一意条件の衝突を扱うかを示します。DO UPDATE SET name=excluded.nameは、新しい候補の名前を使って既存行のnameを更新する指定です。

更新する列を明示できるため、登録日時など、変えたくない列まで入れ直さずに済む設計にできます。ただし、更新すれば常に安全という意味ではありません。更新トリガーや同時更新の条件も確認します。

UPSERTは一意制約の衝突を扱う仕組みです。必須項目、CHECK、外部キーなどの不備まで一律に直す機能ではありません。エラーを避ける構文として広く使わず、どの衝突なら更新してよいかを先に決めます。

入れ直す前に関連を確認

既存の台帳へREPLACEを使う前には、そのテーブルを参照する子テーブルと、削除時の設定を調べます。試す場合も、実データへ直接実行せず、コピーした検証用データで前後の件数を比べます。

再取り込みの仕様として、既存行のどの列を維持するか、入力にない行を残すか、同じ入力を二度処理してもどうなるかも整理します。同じIDが来たという理由だけで、削除や再登録の判断まで進めないようにします。

SQLの基本はSQLite入門、同じIDをどう判定するかはデータの重複判定で補えます。

参考資料

SQLite UPSERT、ON CONFLICT、外部キーの公式資料を2026年11月1日に確認しました。