SQLite UPSERT / UPDATE OU INSERT

Je dois effectuer UPSERT / INSERT OU UPDATE sur une firebase database SQLite.

Il y a la commande INSERT OR REPLACE qui, dans de nombreux cas, peut être utile. Mais si vous souhaitez conserver votre identifiant avec incrémentation automatique à cause des clés étrangères, cela ne fonctionne pas car il supprime la ligne, en crée une nouvelle et par conséquent, cette nouvelle ligne a un nouvel identifiant.

Ce serait la table:

players – (clé primaire sur l’ID, nom d’utilisateur unique)

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

C’est une réponse tardive. À partir de SQLIte 3.24.0, publié le 4 juin 2018, il existe enfin un support pour la clause UPSERT suivant la syntaxe PostgreSQL.

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

Style de Q & A

Eh bien, après avoir recherché et lutté contre le problème pendant des heures, j’ai découvert qu’il y a deux manières d’y parvenir, selon la structure de votre table et si vous avez activé des ressortingctions de clés étrangères pour maintenir l’intégrité. Je voudrais partager ceci dans un format propre pour gagner du temps aux personnes qui peuvent être 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é pour qu’il n’y ait aucune exception d’intégrité. La voie à suivre est INSERT OU REPLACE . Si vous essayez d’insérer / mettre à jour un lecteur 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 conserver l’ancien identifiant 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) 

Le tour est en fusion. Il renvoie l’identifiant de l’utilisateur ‘steven’ s’il existe, et renvoie un nouvel identifiant.

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

Après m’être familiarisé avec la solution précédente, j’ai réalisé que dans mon cas, cela pourrait finir par détruire les données, puisque cet identifiant fonctionne comme une clé étrangère pour une autre table. De plus, j’ai créé la table avec la clause ON DELETE CASCADE , ce qui signifierait qu’elle supprime les données en silence. Dangereux.

Donc, j’ai d’abord pensé à une clause IF, mais SQLite n’a que CASE . Et ce CASE ne peut pas être utilisé (ou du moins je ne l’ai pas géré) pour exécuter une requête UPDATE si EXISTS (sélectionnez l’ID des joueurs où user_name = ‘steven’) et INSERT si ce n’est pas le cas. Ne pas aller.

Et puis, finalement, j’ai utilisé la force brute, avec succès. La logique est la suivante: pour chaque UPSERT que vous voulez exécuter, exécutez d’abord un INSERT OR IGNORE pour vous assurer qu’il y a une ligne avec notre utilisateur, puis exécutez une requête UPDATE avec exactement les mêmes données que vous avez essayé d’insérer.

Mêmes données que précédemment: user_name = ‘steven’ et age = 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!

MODIFIER

Comme Andy l’a commenté, essayer d’insérer d’abord, puis mettre à jour peut conduire à des déclenchements plus souvent que prévu. Ce n’est pas à mon avis un problème de sécurité des données, mais il est vrai que tirer des événements inutiles n’a pas beaucoup de sens. Par conséquent, une solution améliorée serait:

 -- Try to update any existing row UPDATE players SET user_name='steven', age=32 WHERE user_name='steven'; -- Make sure it exists INSERT OR IGNORE INTO players (user_name, age) VALUES ('steven', 32); 

Voici une approche qui n’exige pas que la force brute “ignore”, qui ne fonctionnerait que s’il y avait une violation clé. Cette méthode fonctionne en fonction des conditions spécifiées dans la mise à jour.

Essaye ça…

 -- Try to update any existing row UPDATE players SET user_name='steven', age=32 WHERE user_name='steven'; -- If no update happened (ie 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 “magie” vous permet d’utiliser la clause Where (Select Changes() = 0) pour déterminer s’il existe des lignes pour l’insertion, et puisque cela est basé sur votre propre clause Where , cela peut être pour tout ce que vous définissez, pas juste des violations clés.

Dans l’exemple ci-dessus, s’il n’y a pas de changement depuis la mise à jour (l’enregistrement n’existe pas), alors Changes() = 0 pour que la clause Where de l’instruction Insert retourne true et une nouvelle ligne contenant les données spécifiées.

Si la Update a mis à jour une ligne existante, alors Changes() = 1, de sorte que la clause “Where” de l’ Insert sera désormais fausse et qu’aucune insertion n’aura lieu.

Pas de force brute nécessaire.

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 aux deux déclencheurs exécutés (pour l’insertion et la mise à jour) lorsque la ligne n’existe pas.

La bonne solution est

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

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

Avoir un UPSERT pur sans trous (pour les programmeurs) qui ne transmettent pas les clés uniques et autres:

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

SELECT changes () renverra le nombre de mises à jour effectuées dans la dernière demande. Ensuite, vérifiez si la valeur de retour de changes () est 0, si si exécutez:

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

Option 1: Insérer -> Mise à jour

Si vous souhaitez éviter les deux changes()=0 et INSERT OR IGNORE même si vous ne pouvez pas vous permettre de supprimer la ligne, vous pouvez utiliser cette logique.

Tout d’abord, insérez (s’il n’existe pas), puis mettez à jour en filtrant avec la clé unique.

Exemple

 -- Table structure CREATE TABLE players ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_name VARCHAR (255) NOT NULL UNIQUE, age INTEGER NOT NULL ); -- Insert if NOT exists INSERT INTO players (user_name, age) SELECT 'johnny', 20 WHERE NOT EXISTS (SELECT 1 FROM players WHERE user_name='johnny' AND age=20); -- Update (will affect row, only if found) -- no point to update user_name to 'johnny' since it's unique, and we filter by it as well UPDATE players SET age=20 WHERE user_name='johnny'; 

En ce qui concerne les déclencheurs

Remarque: je ne l’ai pas testé pour voir quels déclencheurs sont appelés, mais je suppose ce qui suit:

si la ligne n’existe pas

  • AVANT D’INSERER
  • INSERT en utilisant INSTEAD OF
  • APRÈS INSERT
  • AVANT MISE À JOUR
  • MISE À JOUR en utilisant INSTEAD OF
  • APRÈS LA MISE À JOUR

si la ligne existe

  • AVANT MISE À JOUR
  • MISE À JOUR en utilisant INSTEAD OF
  • APRÈS LA MISE À JOUR

Option 2: Insérer ou remplacer – conservez votre propre identifiant

de cette façon, vous pouvez avoir une seule commande SQL

 -- Table structure CREATE TABLE players ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_name VARCHAR (255) NOT NULL UNIQUE, age INTEGER NOT NULL ); -- Single command to insert or update INSERT OR REPLACE INTO players (id, user_name, age) VALUES ((SELECT id from players WHERE user_name='johnny' AND age=20), 'johnny', 20); 

Modifier: option ajoutée 2.

Vous pouvez aussi simplement append une clause ON CONFLICT REPLACE à votre contrainte unique nom_utilisateur, puis simplement INSERT, laissant à SQLite le soin de déterminer ce qu’il faut faire en cas de conflit. Voir: https://sqlite.org/lang_conflict.html .

Notez également la phrase concernant les déclencheurs de suppression: Lorsque la stratégie de résolution de conflit REPLACE supprime les lignes afin de satisfaire une contrainte, les déclencheurs sont supprimés si et seulement si les déclencheurs récursifs sont activés.

La réponse acceptée n’est pas correcte
en raison de sa requête 2
son complexe seulement !!

c’est simple 2 requête:

 $check=query('select id from players where user_name="steven";'); if(empty($check)) { query('insert into players (user_name,age) values ("steven",32);'); } else { query('update players set age=13 where id='.$check['id'].';'); } 

  • requête est une fonction par exemple