SQLite UPSERT / UPDATE OU INSERT

Question

J'ai besoin d'effectuer un UPSERT / INSERT OU UPDATE sur une base de données SQLite.

Il existe la commande INSERT OR REPLACE qui, dans de nombreux cas, peut être utile. Mais si vous voulez conserver vos identifiants avec auto-incrément en place à cause des clés étrangères, cela ne fonctionne pas car cela supprime la ligne, en crée une nouvelle et par conséquent cette nouvelle ligne a un nouvel identifiant.

Ce serait la table :

joueurs - (clé primaire sur id, user_name unique)

|  id   | user_name |  age   |
------------------------------
|  1982 |   johnny  |  23    |
|  1983 |   steven  |  29    |
|  1984 |   pepee   |  40    |

Answer 1

C'est une réponse tardive. À partir de la version 3.24.0 de SQLIte, publiée le 4 juin 2018, il y a finalement un support pour UPSERT en suivant la syntaxe PostgreSQL.

INSERT INTO players (user_name, age)
  VALUES('steven', 32) 
  ON CONFLICT(user_name) 
  DO UPDATE SET age=excluded.age;

Note : Pour ceux qui doivent utiliser une version de SQLite antérieure à 3.24.0, veuillez vous référer à cette réponse ci-dessous (posté par moi, @MarqueIV).

Cependant, si vous avez la possibilité de faire une mise à niveau, vous êtes fortement encouragé de le faire, car contrairement à ma solution, celle postée ici permet d'obtenir le comportement souhaité en une seule déclaration. De plus, vous bénéficiez de toutes les autres fonctionnalités, améliorations et corrections de bogues qui accompagnent généralement une version plus récente.

Answer 2

Style Q&A

Eh bien, après avoir fait des recherches et m'être battu avec le problème pendant des heures, j'ai découvert qu'il y a deux façons d'y parvenir, en fonction de la structure de votre table et si vous avez des restrictions de clés étrangères activées pour maintenir l'intégrité. J'aimerais partager cette information dans un format simple pour faire gagner du temps aux personnes qui se trouvent dans ma situation.

Option 1 : Vous pouvez vous permettre de supprimer la ligne.

En d'autres termes, vous n'avez pas de clé étrangère, ou si vous en avez, votre moteur SQLite est configuré de manière à ce qu'il n'y ait pas d'exceptions d'intégrité. La solution est la suivante INSÉRER OU REMPLACER . Si vous essayez d'insérer/mettre à jour un joueur dont l'ID existe déjà, le moteur SQLite supprimera cette ligne et insérera les données que vous fournissez. Maintenant la question se pose : que faire pour garder l'ancien ID associé ?

Disons que nous voulons UPSERT avec les données user_name='steven' et age=32.

Regardez ce code :

INSERT INTO players (id, name, age)

VALUES (
    coalesce((select id from players where user_name='steven'),
             (select max(id) from drawings) + 1),
    32)

L'astuce est dans la coalescence. Elle renvoie l'identifiant de l'utilisateur 'steven' s'il existe, et sinon, elle renvoie un nouvel identifiant.

Option 2 : Vous ne pouvez pas vous permettre de supprimer la ligne.

Après avoir bricolé avec la solution précédente, j'ai réalisé que dans mon cas, cela pourrait détruire des données, puisque cet ID fonctionne comme une clé étrangère pour une autre table. Par ailleurs, j'ai créé la table avec la clause SUR LA SUPPRESSION EN CASCADE ce qui signifie qu'il supprimerait les données en silence. Dangereux.

J'ai d'abord pensé à une clause IF, mais SQLite ne dispose que de CASE . Et ceci CASE ne peut pas être utilisé (ou du moins je n'y suis pas parvenu) pour effectuer un UPDATE requête if EXISTS(select id from players where user_name='steven'), et INSERT si ce n'était pas le cas. Pas question.

Et puis, finalement, j'ai utilisé la force brute, avec succès. La logique est la suivante : pour chaque UPSERT que vous souhaitez effectuer, exécutez d'abord une commande INSÉRER OU IGNORER pour s'assurer qu'il y a une rangée avec notre utilisateur, et ensuite exécuter un UPDATE avec exactement les mêmes données que vous avez essayé d'insérer.

Mêmes données que précédemment : nom d'utilisateur='steven' et âge=32.

-- make sure it exists
INSERT OR IGNORE INTO players (user_name, age) VALUES ('steven', 32); 

-- make sure it has the right data
UPDATE players SET user_name='steven', age=32 WHERE user_name='steven'; 

Et c'est tout !

EDIT

Comme Andy l'a fait remarquer, le fait d'essayer d'insérer d'abord et de mettre à jour ensuite peut entraîner le déclenchement de déclencheurs plus souvent que prévu. À mon avis, ce n'est pas un problème de sécurité des données, mais il est vrai que déclencher des événements inutiles n'a pas beaucoup de sens. Par conséquent, une solution améliorée serait la suivante :

-- Try to update any existing row
UPDATE players SET age=32 WHERE user_name='steven';

-- Make sure it exists
INSERT OR IGNORE INTO players (user_name, age) VALUES ('steven', 32);

Answer 3

Voici une approche qui ne nécessite pas d'ignorer par la force brute, ce qui ne fonctionnerait qu'en cas de violation de la clé. Cette méthode fonctionne sur la base tout les conditions que vous spécifiez dans la mise à jour.

Essayez ceci...

-- Try to update any existing row
UPDATE players
SET age=32
WHERE user_name='steven';

-- If no update happened (i.e. the row didn't exist) then insert one
INSERT INTO players (user_name, age)
SELECT 'steven', 32
WHERE (Select Changes() = 0);

Comment ça marche

La "sauce magique" ici consiste à utiliser Changes() dans le Where clause. Changes() représente le nombre de lignes affectées par la dernière opération, qui dans ce cas est la mise à jour.

Dans l'exemple ci-dessus, si la mise à jour n'entraîne aucun changement (c'est-à-dire que l'enregistrement n'existe pas), alors Changes() = 0 donc le Where dans les Insert est évaluée à true et une nouvelle ligne est insérée avec les données spécifiées.

Si le Update a fait mettre à jour une ligne existante, alors Changes() = 1 (ou plus précisément, non nul si plus d'une ligne a été mise à jour), de sorte que la clause "Where" dans le fichier Insert est maintenant évalué à false et donc aucune insertion n'aura lieu.

L'avantage est qu'il n'est pas nécessaire de recourir à la force brute, ni de supprimer inutilement des données, puis de les réinsérer, ce qui pourrait perturber les clés en aval dans les relations entre clés étrangères.

De plus, étant donné qu'il s'agit d'un standard Where clause, elle peut être basée sur tout ce que vous définissez, et pas seulement sur les principales violations. De même, vous pouvez utiliser Changes() en combinaison avec tout ce que vous voulez/besoin partout où les expressions sont autorisées.

Answer 4

Le problème avec toutes les réponses présentées est l'absence totale de prise en compte des déclencheurs (et probablement d'autres effets secondaires). Solution comme

INSERT OR IGNORE ...
UPDATE ...

conduit à l'exécution des deux triggers (pour l'insertion et ensuite pour la mise à jour) lorsque la ligne n'existe pas.

La solution appropriée est

UPDATE OR IGNORE ...
INSERT OR IGNORE ...

dans ce cas, une seule instruction est exécutée (lorsque la ligne existe ou non).

Answer 5

Pour avoir un pur UPSERT sans trous (pour les programmeurs) qui ne s'appuie pas sur des clés uniques et autres :

UPDATE players SET user_name="gil", age=32 WHERE user_name='george'; 
SELECT changes();

SELECT changes() retournera le nombre de mises à jour effectuées lors de la dernière enquête. Ensuite, vérifiez si la valeur de retour de changes() est 0, si oui, exécutez :

INSERT INTO players (user_name, age) VALUES ('gil', 32);