Réponse

SQL propose différentes manières de mettre à jour ou d'insérer des données dans une table :

UPDATE — modifie les enregistrements existants correspondant à une condition.
MERGE (ou UPSERT) — allie la logique d'insertion et de mise à jour en une seule commande : si la ligne est trouvée — elle est mise à jour ; sinon — elle est ajoutée.
INSERT ... ON DUPLICATE KEY UPDATE (MySQL) ou INSERT ... ON CONFLICT DO UPDATE (PostgreSQL) — insère une nouvelle ligne, et en cas de conflit de clé, met à jour l'existant.

Quand et quoi utiliser :

Si on sait pertinemment que les enregistrements n'existent pas encore — utilisez INSERT.
Pour mettre à jour des enregistrements existants — UPDATE avec conditions.
Si l'on ne sait pas si un enregistrement existe, il est plus efficace d'utiliser MERGE ou UPSERT — cela réduit la complexité et diminue le nombre de requêtes SQL.
Les opérations UPSERT sont généralement plus rapides avec un grand nombre d'enregistrements et minimisent les conflits.

Exemple (PostgreSQL) :

INSERT INTO employees(id, name, salary)
VALUES (1, 'Ivan', 100000)
ON CONFLICT (id) DO UPDATE SET salary = EXCLUDED.salary;

Impact sur les performances et l'intégrité :

Des UPDATE/INSERT séparés multiples peuvent entraîner des conflits et des blocages.
MERGE/UPSERT est plus efficace lors de migrations massives et soutient l'atomicité.

Question piégée

Que se passe-t-il si l'on tente d'effectuer un UPSERT sur un champ unique, mais dans la même transaction insérer et mettre à jour la même ligne en même temps ?

Réponse correcte : Il peut y avoir une erreur de blocage/conflit, car la transaction tentera de modifier la même ligne deux fois. L'opération échouera ou aboutira à une erreur deadlock selon le SGBD.

Exemple :

BEGIN;
INSERT INTO users(id, name) VALUES (1, 'Oleg') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;
UPDATE users SET name = 'Petr' WHERE id = 1;
COMMIT;

Avec un ordre et un niveau d'isolation incorrects, un conflit de modifications peut se produire.

Histoire n°1

Dans une logique de migration de données, une combinaison de SELECT, puis INSERT ou UPDATE était utilisée, ce qui entraînait parfois une autre transaction qui définissait les données plus tôt, provoquant l'erreur « clé dupliquée ». Cela a entraîné des échecs fréquents lors de l'exportation de données nocturne, nécessitant de remplacer la logique par UPSERT.

Histoire n°2

Dans un projet MySQL, ON DUPLICATE KEY UPDATE était appliqué de manière incorrecte, ignorant les clés uniques composées. En conséquence, certaines données n'étaient pas mises à jour et des doublons apparaissaient. Le problème a été détecté seulement sur l'environnement de production.

Histoire n°3

Dans un projet, UPDATE était utilisé à la place d'UPSERT pour synchroniser les données utilisateur. Lors de pannes de connexion, certaines données étaient perdues, car la mise à jour était ignorée pour les nouveaux utilisateurs. Après une analyse, MERGE/UPSERT a été complètement implémenté.

Expliquez les différences entre les commandes UPDATE, MERGE et INSERT ... ON DUPLICATE KEY UPDATE en SQL. Dans quels cas chaque approche est-elle préférable, et comment ces outils impactent-ils les performances et l'intégrité des données ?

Réponse

Question piégée