SQLite UPSERT : ON CONFLICT DO UPDATE et DO NOTHING

Insérer, ou mettre à jour si la ligne existe déjà

Un besoin classique : insérer une ligne, mais si une ligne avec la même clé existe déjà, la mettre à jour à la place. Sans UPSERT, il faut d'abord faire un SELECT, puis bifurquer vers un INSERT ou un UPDATE — deux allers-retours, plus une jolie race condition entre les deux.

L'UPSERT de SQLite fait tout ça en une seule instruction :

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products (sku, name, price)
VALUES ('A-100', 'Bidule', 9.99)
ON CONFLICT(sku) DO UPDATE SET
    name  = excluded.name,
    price = excluded.price;

SELECT * FROM products;

Au premier lancement, la ligne est insérée. Relancez avec un prix différent et le même sku : la ligne existante est mise à jour sur place. Pas de doublon, pas d'erreur.

Anatomie de la clause ON CONFLICT

Voici la structure complète :

INSERT INTO table (...) VALUES (...)
ON CONFLICT(conflict_target) DO UPDATE SET col = expr, ...
WHERE condition;

Trois éléments comptent vraiment :

• conflict_target — la ou les colonnes portant une contrainte UNIQUE ou PRIMARY KEY sur laquelle vous prévoyez la collision. SQLite s'en sert pour choisir l'index à surveiller.
• DO UPDATE SET ... — ce qu'il faut modifier sur la ligne existante en cas de collision. (Ou DO NOTHING pour ignorer en silence.)
• WHERE optionnel — une condition supplémentaire qui doit être vraie pour que la mise à jour s'applique réellement.

La cible du conflit doit correspondre à une vraie contrainte d'unicité. ON CONFLICT(price) ne passera pas à la compilation si price n'est pas unique — SQLite n'a alors rien sur quoi détecter un conflit.

DO NOTHING : insérer si absent, sinon ignorer

La variante la plus simple. Pratique quand vous initialisez des données ou enregistrez des événements et que les doublons doivent simplement être ignorés sans bruit :

CREATE TABLE seen_events (
    event_id TEXT PRIMARY KEY,
    seen_at  TEXT DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO seen_events (event_id) VALUES ('evt-1')
ON CONFLICT(event_id) DO NOTHING;

INSERT INTO seen_events (event_id) VALUES ('evt-1')
ON CONFLICT(event_id) DO NOTHING;

INSERT INTO seen_events (event_id) VALUES ('evt-2')
ON CONFLICT(event_id) DO NOTHING;

SELECT * FROM seen_events;

Le second INSERT cible le même event_id et déclencherait normalement une erreur UNIQUE constraint failed. Avec DO NOTHING, SQLite passe simplement son chemin. Pas d'exception, aucune ligne modifiée.

C'est le fameux « insert idempotent », pour lequel beaucoup dégainent par réflexe INSERT OR IGNORE. Le DO NOTHING de l'UPSERT fait exactement le même boulot, tout en se mariant bien mieux avec les clauses WHERE et RETURNING.

La pseudo-table excluded

Quand un conflit se déclenche, vous vous retrouvez avec deux lignes en jeu : celle qui existe déjà dans la table, et la nouvelle que vous tentiez d'insérer. SQLite vous donne de quoi désigner chacune des deux.

• Les noms de colonnes nus (price, name) désignent la ligne existante.
• excluded.column désigne la ligne entrante qui a été rejetée.

CREATE TABLE inventory (
    sku      TEXT PRIMARY KEY,
    quantity INTEGER NOT NULL,
    updated  TEXT
);

INSERT INTO inventory (sku, quantity, updated)
VALUES ('A-100', 5, '2026-04-01')
ON CONFLICT(sku) DO UPDATE SET
    quantity = quantity + excluded.quantity,
    updated  = excluded.updated;

INSERT INTO inventory (sku, quantity, updated)
VALUES ('A-100', 3, '2026-04-02')
ON CONFLICT(sku) DO UPDATE SET
    quantity = quantity + excluded.quantity,
    updated  = excluded.updated;

SELECT * FROM inventory;

quantity = quantity + excluded.quantity se lit comme « la quantité existante plus la nouvelle ». Après deux insertions, A-100 affiche une quantité de 8. Ce schéma — cumuler une valeur dans une ligne déjà présente — fait partie des astuces les plus pratiques de l'UPSERT.

UPSERT conditionnel avec WHERE

La clause WHERE finale permet de zapper la mise à jour tant qu'une condition n'est pas remplie. Elle s'évalue sur la ligne existante (et peut faire référence à excluded.* pour la ligne entrante) :

CREATE TABLE prices (
    sku        TEXT PRIMARY KEY,
    price      REAL NOT NULL,
    updated_at TEXT NOT NULL
);

INSERT INTO prices VALUES ('A-100', 10.00, '2026-04-01');

INSERT INTO prices (sku, price, updated_at)
VALUES ('A-100', 12.00, '2026-03-15')
ON CONFLICT(sku) DO UPDATE SET
    price      = excluded.price,
    updated_at = excluded.updated_at
WHERE excluded.updated_at > prices.updated_at;

SELECT * FROM prices;

La nouvelle ligne arrive avec un updated_at plus ancien : la clause WHERE est donc fausse et la mise à jour est ignorée. La ligne existante conserve son prix plus récent. Inversez les dates et la mise à jour s'exécute. C'est le pattern classique « ne réécrire qu'avec des données plus fraîches ».

Upsert de plusieurs lignes en SQLite

VALUES accepte plusieurs lignes d'un coup, et ON CONFLICT s'applique à chacune indépendamment :

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products VALUES ('A-100', 'Widget', 9.99);

INSERT INTO products (sku, name, price) VALUES
    ('A-100', 'Widget v2', 10.99),
    ('A-200', 'Gadget',     14.99),
    ('A-300', 'Gizmo',      19.99)
ON CONFLICT(sku) DO UPDATE SET
    name  = excluded.name,
    price = excluded.price;

SELECT * FROM products ORDER BY sku;

A-100 entre en conflit et se voit mis à jour. A-200 et A-300 sont nouveaux : ils sont insérés. Une seule requête, avec un résultat mixte insert/update. C'est une façon propre de synchroniser un lot d'enregistrements provenant d'une source externe.

UPSERT ou INSERT OR REPLACE : quelle différence ?

À première vue, INSERT OR REPLACE semble faire exactement la même chose. Détrompez-vous.

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT,
    notes TEXT
);

INSERT INTO users VALUES (1, 'Ada', 'ada@example.com', 'Cliente VIP');

-- Remplacement : supprime l'ancienne ligne, en insère une nouvelle.
INSERT OR REPLACE INTO users (id, name, email)
VALUES (1, 'Ada Lovelace', 'ada@example.com');

SELECT * FROM users;

notes a disparu. INSERT OR REPLACE a tout bonnement supprimé la ligne 1 pour en insérer une toute neuve — les colonnes que vous n'avez pas mentionnées sont remises à NULL ou à leur valeur par défaut. Au passage, ça déclenche aussi les triggers DELETE et fait jouer les cascades ON DELETE des clés étrangères.

L'UPSERT, lui, conserve la ligne :

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT,
    notes TEXT
);

INSERT INTO users VALUES (1, 'Ada', 'ada@example.com', 'Cliente VIP');

INSERT INTO users (id, name, email)
VALUES (1, 'Ada Lovelace', 'ada@example.com')
ON CONFLICT(id) DO UPDATE SET
    name  = excluded.name,
    email = excluded.email;

SELECT * FROM users;

notes est toujours là. Seules les colonnes mentionnées dans SET ont changé. Par défaut, optez pour l'UPSERT ; ne sortez INSERT OR REPLACE que si vous voulez vraiment la sémantique « supprimer puis réinsérer ».

Plusieurs cibles de conflit

Si une ligne peut entrer en collision sur plusieurs contraintes à la fois, vous pouvez enchaîner les clauses ON CONFLICT :

CREATE TABLE accounts (
    id       INTEGER PRIMARY KEY,
    username TEXT UNIQUE,
    email    TEXT UNIQUE,
    score    INTEGER DEFAULT 0
);

INSERT INTO accounts (username, email, score)
VALUES ('rosa', 'rosa@example.com', 10)
ON CONFLICT(username) DO UPDATE SET score = score + excluded.score
ON CONFLICT(email)    DO UPDATE SET score = score + excluded.score;

INSERT INTO accounts (username, email, score)
VALUES ('rosa', 'rosa@example.com', 5)
ON CONFLICT(username) DO UPDATE SET score = score + excluded.score
ON CONFLICT(email)    DO UPDATE SET score = score + excluded.score;

SELECT * FROM accounts;

C'est la première contrainte déclenchée qui l'emporte, et c'est le DO UPDATE de cette branche qui s'exécute. En pratique, la plupart des tables ont une cible de conflit évidente — la clé primaire ou une seule colonne unique — et on a rarement besoin de plus d'une clause.

Pièges classiques de l'upsert SQLite

Voici quelques trucs qui font trébucher pas mal de monde :

• Pas d'index unique correspondant, pas d'UPSERT. ON CONFLICT(col) exige que col soit une PRIMARY KEY ou porte une contrainte UNIQUE. Sinon, SQLite renvoie une erreur du type "no such constraint".
• DO UPDATE ne se déclenche pas en l'absence de conflit. C'est une alternative à l'insertion, pas un comportement supplémentaire. La première fois qu'une clé apparaît, seul l'INSERT est exécuté.
• excluded est en lecture seule. On peut y lire des valeurs, mais pas y écrire. La cible du SET, c'est toujours la ligne existante.
• Les rowids générés via INTEGER PRIMARY KEY. Si vous ne fournissez pas l'id, chaque insertion en reçoit un nouveau — il n'y a donc rien avec quoi entrer en conflit. L'UPSERT n'a de sens que si la colonne de conflit reçoit une valeur déterministe fournie par l'appelant.

La suite : RETURNING

L'UPSERT ne vous dit rien sur les lignes insérées par rapport à celles mises à jour, ni sur leurs valeurs finales. Pour ça, il y a la clause RETURNING : elle vous renvoie les lignes affectées dans la même requête, sans avoir à enchaîner avec un SELECT. C'est ce qu'on voit ensuite.