SQL ON DUPLICATE KEY UPDATE

L’instruction ON DUPLICATE KEY UPDATE est une fonctionnalité de MySQL qui permet de mettre à jour des données lorsqu’un enregistrement existe déjà dans une table. Cela permet d’avoir qu’une seule requête SQL pour effectuer selon la convenance un INSERT ou un UPDATE.

Syntaxe

Cette commande s’effectue au sein de la requête INSERT INTO avec la syntaxe suivante:

INSERT INTO table (a, b, c)
VALUES (1, 20, 68)
ON DUPLICATE KEY UPDATE a=a+1

A noter : cette requête se traduit comme suit :

1. insérer les données a, b et c avec les données respectives de 1, 20 et 68
2. Si la clé primaire existe déjà pour ces valeurs alors seulement faire une mise à jour de a = a+1

Exemple avec la commande WHERE

Grâce à la commande “ON DUPLICATE KEY” Il est possible d’enregistrer la date à laquelle la données est insérée pour la première fois et la date de dernière mise à jour, comme le montre la commande ci-dessous:

INSERT INTO table (a, b, c, date_insert)
VALUES (1, 20, 1, NOW())
ON DUPLICATE KEY UPDATE date_update=NOW
WHERE c=1

A noter : cette requête se traduit comme suit :

1. insérer les données a, b, c et date_insert, avec les données respectives de 1, 20, 1 ainsi que la date et l’heure actuelle
2. Si la clé primaire existe déjà pour ces valeurs alors mettre a jour la date et l’heure du champ “date_update”
3. Effectuer la mise à jour uniquement sur les champs où c = 1

Exemple

Imaginons une application qui laisse les utilisateurs voter pour les produits qu’ils préfèrent. Le système de vote est très simple et est basé sur des +1. La table des votes contient le nombre de votes par produits avec la date du premier vote et la date du dernier vote.

Table vote :

Pour n’utiliser qu’une seule ligne qui permet d’ajouter des votes dans cette table, sans se préoccuper de savoir s’il faut faire un INSERT ou un UPDATE, il est possible d’utiliser la requête SQL suivante:

INSERT INTO vote (produit_id, vote_count, vote_first_date, vote_last_date)
VALUES (50, 1, NOW(), NOW())
ON DUPLICATE KEY UPDATE vote_count = vote_count+1, vote_last_date = NOW()

Dans cette requête la date et l’heure est générée automatiquement avec la fonction NOW().

Résultat après la première exécution de la requête:

Ce résultat montre bien l’ajout d’une ligne en fin de table, donc la requête a été utilisé sous la forme d’un INSERT. Après une deuxième exécution de cette même requête le lendemain, les données seront celles-ci:

Ces résultats montre bien qu’il y a eu un vote supplémentaire et que la date du dernier vote a été mis à jour.

Insérer une ligne ou ne rien faire

Dans certains cas il est intéressant d’utiliser un INSERT mais de ne rien faire si la commande a déjà été insérée précédemment. Malheureusement, si la clé primaire existe déjà la requête retournera une erreur. Et s’il n’y a rien à mettre à jour, la commande ON DUPLICATE KEY UPDATE (ODKU) ne semble pas convenir. Toutefois il y a une astuce qui consiste à utiliser une requête de ce type:

INSERT INTO table (a, b, c)
VALUES (1, 45, 6)
ON DUPLICATE KEY UPDATE id = id

Cette requête insert les données et ne produit aucune erreur si l’enregistrement existait déjà dans la table.

A savoir : théoriquement il aurait été possible d’utiliser INSERT IGNORE mais malheureusement cela empêche de retourner des erreurs telles que des erreurs de conversions de données.

Compatibilité

Pour le moment cette fonctionnalité n’est possible qu’avec MySQL depuis la version 4.1 (date de 2003). Les autres Systèmes de Gestion de Bases de Données (SGBD) n’intègrent pas cette fonctionnalité. Pour simuler cette fonctionnalité il y a quelques alternatives:

• PostgreSQL : il y a une astuce en utilisant une fonction. L’astuce est expliquée dans la documentation officielle : fonction INSERT/UPDATE.
• Oracle : il est possible d’utiliser la commande MERGE pour effectuer la même chose.
• SQL Server : il est possible d’utiliser une procédure.