UPSERT (ON CONFLICT) et applications du bulk INSERT

Les données qu'on utilise — stock et stock_in

L'UPSERT (un mélange d'UPDATE et d'INSERT —

l'opération « mettre à jour si présent, insérer sinon » en une seule instruction) s'implémente via la syntaxe INSERT … ON CONFLICT(cle) DO UPDATE.

Si la ligne entre en collision avec une clé primaire ou une contrainte UNIQUE, la mise à jour s'exécute ; sinon, elle est insérée telle quelle.

Avant les exercices, vérifie les définitions de colonnes et les données d'exemple des deux tables — stock et stock_in.

① Exécute PRAGMA table_info(stock); pour voir les noms de colonnes, les types et la clé primaire de stock (UPSERT suppose que sku est la clé primaire).

② Prévisualise les deux tables avec SELECT * FROM stock ORDER BY sku; et SELECT * FROM stock_in;.

ON CONFLICT DO UPDATE — mettre à jour si présent, insérer sinon

Écrire INSERT INTO table(...) VALUES (...) ON CONFLICT(col_cle) DO UPDATE SET col = ... signifie : si la ligne que tu tentes d'insérer entre en collision avec la clé déclarée dans ON CONFLICT (la clé primaire ou une colonne UNIQUE), la mise à jour DO UPDATE s'exécute ; sans collision, la ligne est simplement insérée.

À l'intérieur de DO UPDATE, un nom de table spécial excluded te permet de référencer « les valeurs de la ligne que tu tentais d'insérer ».

Écris qty = qty + excluded.qty et, en cas de collision, le qty existant est incrémenté du qty que tu tentais d'insérer.

Utilise qty = excluded.qty pour l'écrasement et qty = qty + excluded.qty pour l'accumulation, selon ton besoin.

-- Collision avec un sku existant → DO UPDATE ajoute au qty
INSERT INTO stock(sku, name, qty, price)
VALUES ('A001', 'Pen', 5, 80)
ON CONFLICT(sku) DO UPDATE SET qty = qty + excluded.qty;

-- Nouveau sku → pas de collision, simple insertion
INSERT INTO stock(sku, name, qty, price)
VALUES ('A006', 'Marker', 3, 120)
ON CONFLICT(sku) DO UPDATE SET qty = qty + excluded.qty;

SELECT sku, name, qty FROM stock WHERE sku IN ('A001', 'A006');

Imagine le besoin : « dans le traitement des arrivages, ajouter au stock pour les sku existants, et enregistrer les sku inconnus comme neufs. » (Exécute correctement et l'explication apparaît.)

① Avant l'UPSERT, exécute SELECT sku, name, qty FROM stock WHERE sku IN ('A001','A006') ORDER BY sku; pour confirmer qu'A001 existe et qu'A006 non.

② Pour l'A001 existant, UPSERT avec name Pen, qty 15, price 80. En cas de collision, ajoute la valeur insérée au qty existant (sans écraser).

③ Pour l'A006 inconnu, exécute le même type d'UPSERT avec name Stapler, qty 3, price 200 (pas de collision, donc inséré).

④ Enfin, relance le même SELECT et confirme qu'A001 a été incrémenté et qu'A006 a été inséré.

DO NOTHING — ne rien faire en cas de collision

Quand tu ne veux ni mettre à jour ni insérer — c'est-à-dire « ignorer silencieusement en cas de collision » — utilise ON CONFLICT(cle) DO NOTHING.

Sans collision la ligne est insérée ; en cas de collision la ligne est ignorée sans erreur.

Dans l'exemple ci-dessous, tenter d'insérer l'A002 existant avec DO NOTHING entre en collision et est ignoré, tandis que le nouvel A007 est inséré.

-- Ignorer en cas de collision (DO NOTHING)
INSERT INTO stock(sku, name, qty, price)
VALUES ('A002', 'Note', 999, 999)
ON CONFLICT(sku) DO NOTHING;

-- A002 garde ses valeurs initiales (qty 60 / price 250)
SELECT sku, name, qty, price FROM stock WHERE sku = 'A002';

Imagine le besoin : « lors de l'ingestion de plusieurs lignes dans le master stock, ne jamais écraser les sku existants et n'ajouter que les inconnus. »

① Avant l'UPSERT, exécute SELECT sku, name, qty, price FROM stock WHERE sku IN ('A003','A007') ORDER BY sku; pour confirmer qu'A003 existe et qu'A007 non.

② Tente d'insérer l'A003 existant avec name Clip, qty 0, price 0, en utilisant DO NOTHING pour qu'il soit ignoré en cas de collision.

③ Ajoute l'A007 inconnu avec name Eraser, qty 50, price 90 via le même UPSERT DO NOTHING.

④ Enfin, relance le même SELECT et confirme qu'A003 est inchangé et qu'A007 a été ajouté.

UPSERT multi-lignes — combiner un INSERT en masse avec ON CONFLICT

Quand tu attaches ON CONFLICT à un INSERT multi-lignes — VALUES (...),(...),(...) séparés par des virgules — chaque ligne « met à jour en cas de collision, insère sinon » de façon indépendante.

Tu peux répercuter les données d'arrivage en une instruction, remplaçant une boucle procédurale qui branche entre UPDATE et INSERT par ligne par un seul SQL.

Même dans un UPSERT multi-lignes, excluded désigne toujours « la valeur que tu tentais d'insérer pour cette ligne », donc écrire qty = qty + excluded.qty applique « ajout pour les lignes existantes, insertion telle quelle pour les nouvelles » ligne par ligne.

L'exercice final de cet article est un UPSERT en masse des 3 lignes de stock_in (A001 / A004 / A006).

-- UPSERT multi-lignes : les lignes existantes accumulent qty et rafraîchissent price ; les nouvelles sont insérées
INSERT INTO stock(sku, name, qty, price)
VALUES
  ('A002', 'Note', 10, 260),
  ('A005', 'Glue', 20, 190),
  ('A007', 'Ruler', 15, 90)
ON CONFLICT(sku) DO UPDATE
  SET qty = qty + excluded.qty,
      price = excluded.price;

SELECT sku, name, qty, price FROM stock ORDER BY sku;

Imagine le besoin : « répercuter les 3 lignes de la table d'arrivages stock_in dans stock en une seule instruction. » C'est l'exercice final de l'article.

① Avant l'UPSERT, exécute SELECT sku, name, qty FROM stock WHERE sku IN ('A001','A004','A006') ORDER BY sku; pour confirmer qu'A001 / A004 existent et qu'A006 non.

② Contre stock, insère 3 lignes dans un seul INSERT — A001 (name Pen / qty 50 / price 80), A004 (name Tape / qty 100 / price 150), A006 (name Marker / qty 30 / price 120) — et écris un UPSERT en masse avec ON CONFLICT(sku) DO UPDATE qui ajoute à qty en cas de collision.

③ Enfin, place SELECT sku, name, qty FROM stock ORDER BY sku; et confirme qu'A001 / A004 ont été incrémentés et qu'A006 a été ajouté.