SQLite – UPSERT * not * INSERT ou REPLACE

http://en.wikipedia.org/wiki/Upsert

Insérer la mise à jour stockée proc sur SQL Server

Existe-t-il un moyen astucieux de le faire dans SQLite auquel je n’ai pas pensé?

Fondamentalement, je veux mettre à jour trois colonnes sur quatre si l’enregistrement existe, si ce n’est pas le cas, je veux INSÉRER l’enregistrement avec la valeur par défaut (NUL) pour la quasortingème colonne.

L’ID est une clé primaire, il n’y aura donc qu’un seul enregistrement pour UPSERT.

(J’essaie d’éviter la surcharge de SELECT afin de déterminer si je dois UPDATE ou INSERT évidemment)

Suggestions?


Je ne peux pas confirmer cette syntaxe sur le site SQLite pour TABLE CREATE. Je n’ai pas construit de démo pour le tester, mais cela ne semble pas être supporté.

Si c’était le cas, j’ai trois colonnes pour que cela ressemble à ceci:

CREATE TABLE table1( id INTEGER PRIMARY KEY ON CONFLICT REPLACE, Blob1 BLOB ON CONFLICT REPLACE, Blob2 BLOB ON CONFLICT REPLACE, Blob3 BLOB ); 

mais les deux premiers blobs ne provoqueront pas de conflit, seul l’identifiant aurait pour conséquence que Blob1 et Blob2 ne seraient pas remplacés (comme vous le souhaitez)


UPDATE dans SQLite lorsque la liaison des données est une transaction complète, ce qui signifie que chaque ligne envoyée à mettre à jour nécessite: Préparer / Relier / Etape / Finaliser des instructions contrairement à INSERT qui permet d’utiliser la fonction de réinitialisation

La vie d’un object de déclaration a quelque chose comme ceci:

  1. Créez l’object à l’aide de sqlite3_prepare_v2 ()
  2. Lier les valeurs pour héberger les parameters à l’aide des interfaces sqlite3_bind_.
  3. Exécutez le SQL en appelant sqlite3_step ()
  4. Réinitialisez l’instruction en utilisant sqlite3_reset () puis revenez à l’étape 2 et répétez.
  5. Détruisez l’object instruction à l’aide de sqlite3_finalize ().

UPDATE Je suppose que c’est lent comparé à INSERT, mais comment se compare-t-il à SELECT en utilisant la clé primaire?

Peut-être devrais-je utiliser la sélection pour lire la 4ème colonne (Blob3), puis utiliser REPLACE pour écrire un nouvel enregistrement mélangeant la 4ème colonne d’origine avec les nouvelles données pour les 3 premières colonnes?

En supposant 3 colonnes dans la table .. ID, NOM, ROLE


BAD: Cela va insérer ou remplacer toutes les colonnes avec de nouvelles valeurs pour ID = 1:

 INSERT OR REPLACE INTO Employee (id, name, role) VALUES (1, 'John Foo', 'CEO'); 

BAD: Cela va insérer ou remplacer 2 des colonnes … la colonne NAME sera définie sur NULL ou la valeur par défaut:

 INSERT OR REPLACE INTO Employee (id, role) VALUES (1, 'code monkey'); 

BON: Cela mettra à jour 2 des colonnes. Lorsque ID = 1 existe, le NAME ne sera pas affecté. Lorsque ID = 1 n’existe pas, le nom sera par défaut (NULL).

 INSERT OR REPLACE INTO Employee (id, role, name) VALUES ( 1, 'code monkey', (SELECT name FROM Employee WHERE id = 1) ); 

Cela mettra à jour 2 des colonnes. Lorsque ID = 1 existe, le ROLE ne sera pas affecté. Lorsque ID = 1 n’existe pas, le rôle sera défini sur ‘Benchwarmer’ au lieu de la valeur par défaut.

 INSERT OR REPLACE INTO Employee (id, name, role) VALUES ( 1, 'Susan Bar', COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer') ); 

INSERT OU REPLACE n’est PAS équivalent à “UPSERT”.

Disons que j’ai la table Employee avec les champs id, name et role:

 INSERT OR REPLACE INTO Employee ("id", "name", "role") VALUES (1, "John Foo", "CEO") INSERT OR REPLACE INTO Employee ("id", "role") VALUES (1, "code monkey") 

Boom, vous avez perdu le nom du numéro d’employé 1. SQLite l’a remplacé par une valeur par défaut.

La sortie attendue d’un UPSERT serait de changer le rôle et de conserver le nom.

La réponse d’Eric B est correcte si vous souhaitez conserver une ou peut-être deux colonnes de la ligne existante. Si vous souhaitez conserver un grand nombre de colonnes, cela devient trop fastidieux.

Voici une approche qui s’adaptera à toutes les colonnes de chaque côté. Pour l’illustrer, je supposerai le schéma suivant:

  CREATE TABLE page ( id INTEGER PRIMARY KEY, name TEXT UNIQUE, title TEXT, content TEXT, author INTEGER NOT NULL REFERENCES user (id), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); 

Notez en particulier que name est la clé naturelle de row – id est utilisé uniquement pour les clés étrangères, donc le point est que SQLite sélectionne la valeur de l’ID lui-même lors de l’insertion d’une nouvelle ligne. Mais lors de la mise à jour d’une ligne existante en fonction de son name , je souhaite qu’elle continue d’avoir l’ancienne valeur d’identifiant (évidemment!).

Je réalise un vrai UPSERT avec la construction suivante:

  WITH new (name, title, author) AS ( VALUES('about', 'About this site', 42) ) INSERT OR REPLACE INTO page (id, name, title, content, author) SELECT old.id, new.name, new.title, old.content, new.author FROM new LEFT JOIN page AS old ON new.name = old.name; 

La forme exacte de cette requête peut varier un peu. La clé est l’utilisation de INSERT SELECT avec une jointure externe gauche, pour joindre une ligne existante aux nouvelles valeurs.

Ici, si une ligne n’existait pas auparavant, old.id sera NULL et SQLite affectera automatiquement un identifiant, mais s’il y avait déjà une telle ligne, old.id aura une valeur réelle qui sera réutilisée. Ce qui est exactement ce que je voulais.

En fait, c’est très flexible. Notez que la colonne ts est complètement absente de tous les côtés – car elle a une valeur DEFAULT , SQLite fera tout ce qu’il faut, donc je n’ai pas à m’en occuper moi-même.

Vous pouvez également inclure une colonne sur les deux côtés, puis utiliser par exemple COALESCE(new.content, old.content) dans le SELECT externe pour dire «insérer le nouveau contenu s’il y en avait, sinon conserver l’ancien contenu» – Par exemple, si vous utilisez une requête fixe et que vous liez les nouvelles valeurs avec des espaces réservés.

Si vous faites généralement des mises à jour, je le ferais ..

  1. Commencer une transaction
  2. Faire la mise à jour
  3. Vérifiez le nombre de lignes
  4. Si c’est 0, insérez
  5. Commettre

Si vous faites généralement des insertions, je

  1. Commencer une transaction
  2. Essayez un insert
  3. Vérifier l’erreur de violation de clé primaire
  4. si nous avons une erreur, faites la mise à jour
  5. Commettre

De cette façon, vous évitez la sélection et vous avez un son transactionnel sur Sqlite.

Je me rends compte que c’est un vieux thread, mais je travaille depuis peu sur sqlite3 et j’ai trouvé cette méthode qui correspond mieux à mes besoins de génération dynamic de requêtes paramétrées:

 insert or ignore into (, , , ...) values(, , , ...); update 
set =, =, ... where changes()=0 and =;

Il rest 2 requêtes avec une clause where sur la mise à jour mais semble faire l’affaire. J’ai aussi cette vision dans ma tête que sqlite peut optimiser complètement l’instruction de mise à jour si l’appel aux modifications () est supérieur à zéro. Je ne sais pas si oui ou non, mais un homme peut rêver, n’est-ce pas? 😉

Pour les points bonus, vous pouvez append cette ligne qui vous renvoie l’identifiant de la ligne, qu’il s’agisse d’une ligne nouvellement insérée ou d’une ligne existante.

 select case changes() WHEN 0 THEN last_insert_rowid() else  end; 

2018-05-18 STOP PRESSE.

Assistance UPSERT dans SQLite! La syntaxe UPSERT a été ajoutée à SQLite avec la version 3.24.0 (en attente)!

UPSERT est une addition de syntaxe spéciale à INSERT qui entraîne le comportement de INSERT en tant que UPDATE ou en tant que no-op si INSERT enfreint une contrainte d’unicité. UPSERT n’est pas un SQL standard. UPSERT dans SQLite suit la syntaxe établie par PostgreSQL.

entrer la description de l'image ici

Je sais que je suis en retard à la fête mais …

 UPDATE employee SET role = 'code_monkey', name='fred' WHERE id = 1; INSERT OR IGNORE INTO employee(id, role, name) values (1, 'code monkey', 'fred'); 

Donc, il tente de mettre à jour, si l’enregistrement est là, l’insertion n’est pas action-ed.

alternativement:

Une autre façon complètement différente de procéder est la suivante: Dans mon application, je mets rowID en mémoire à long.MaxValue lorsque je crée la ligne en mémoire. (MaxValue ne sera jamais utilisé comme identifiant que vous ne vivrez pas assez longtemps … Alors si rowID n’est pas cette valeur, alors il doit déjà être dans la firebase database, il a donc besoin d’un insert. Ceci n’est utile que si vous pouvez suivre les rowIDs dans votre application.

Voici une solution qui est vraiment un UPSERT (UPDATE ou INSERT) au lieu d’un INSERT OU REPLACE (qui fonctionne différemment dans de nombreuses situations).

Cela fonctionne comme ceci:
1. Essayez de mettre à jour si un enregistrement avec le même identifiant existe.
2. Si la mise à jour n’a pas changé de ligne ( NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0) ), insérez ensuite l’enregistrement.

Donc, soit un enregistrement existant a été mis à jour ou un insert sera effectué.

Le détail important consiste à utiliser la fonction SQL changes () pour vérifier si l’instruction de mise à jour a frappé des enregistrements existants et n’effectuer l’instruction insert que s’il n’a atteint aucun enregistrement.

Une chose à mentionner est que la fonction changes () ne renvoie pas les modifications effectuées par des déclencheurs de niveau inférieur (voir http://sqlite.org/lang_corefunc.html#changes ), alors assurez-vous de prendre cela en compte.

Voici le SQL …

Mise à jour du test:

 --Create sample table and records (and drop the table if it already exists) DROP TABLE IF EXISTS Contact; CREATE TABLE [Contact] ( [Id] INTEGER PRIMARY KEY, [Name] TEXT ); INSERT INTO Contact (Id, Name) VALUES (1, 'Mike'); INSERT INTO Contact (Id, Name) VALUES (2, 'John'); -- Try to update an existing record UPDATE Contact SET Name = 'Bob' WHERE Id = 2; -- If no record was changed by the update (meaning no record with the same Id existed), insert the record INSERT INTO Contact (Id, Name) SELECT 2, 'Bob' WHERE NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0); --See the result SELECT * FROM Contact; 

Insert de test:

 --Create sample table and records (and drop the table if it already exists) DROP TABLE IF EXISTS Contact; CREATE TABLE [Contact] ( [Id] INTEGER PRIMARY KEY, [Name] TEXT ); INSERT INTO Contact (Id, Name) VALUES (1, 'Mike'); INSERT INTO Contact (Id, Name) VALUES (2, 'John'); -- Try to update an existing record UPDATE Contact SET Name = 'Bob' WHERE Id = 3; -- If no record was changed by the update (meaning no record with the same Id existed), insert the record INSERT INTO Contact (Id, Name) SELECT 3, 'Bob' WHERE NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0); --See the result SELECT * FROM Contact; 

En développant la réponse d’Aristote, vous pouvez sélectionner une table de “singleton” (un tableau de votre propre création avec une seule ligne). Cela évite certaines duplications.

J’ai également conservé l’exemple portable sur MySQL et SQLite et utilisé une colonne ‘date_added’ comme exemple pour définir une colonne uniquement la première fois.

  REPLACE INTO page ( id, name, title, content, author, date_added) SELECT old.id, "about", "About this site", old.content, 42, IFNULL(old.date_added,"21/05/2013") FROM singleton LEFT JOIN page AS old ON old.name = "about"; 

La meilleure approche que je connaisse est de faire une mise à jour, suivie d’une insertion. La “surcharge d’une sélection” est nécessaire, mais ce n’est pas un fardeau terrible puisque vous effectuez une recherche sur la clé primaire, ce qui est rapide.

Vous devriez pouvoir modifier les instructions ci-dessous avec vos noms de table et de champ pour faire ce que vous voulez.

 --first, update any matches UPDATE DESTINATION_TABLE DT SET MY_FIELD1 = ( SELECT MY_FIELD1 FROM SOURCE_TABLE ST WHERE ST.PRIMARY_KEY = DT.PRIMARY_KEY ) ,MY_FIELD2 = ( SELECT MY_FIELD2 FROM SOURCE_TABLE ST WHERE ST.PRIMARY_KEY = DT.PRIMARY_KEY ) WHERE EXISTS( SELECT ST2.PRIMARY_KEY FROM SOURCE_TABLE ST2 ,DESTINATION_TABLE DT2 WHERE ST2.PRIMARY_KEY = DT2.PRIMARY_KEY ); --second, insert any non-matches INSERT INTO DESTINATION_TABLE( MY_FIELD1 ,MY_FIELD2 ) SELECT ST.MY_FIELD1 ,NULL AS MY_FIELD2 --insert NULL into this field FROM SOURCE_TABLE ST WHERE NOT EXISTS( SELECT DT2.PRIMARY_KEY FROM DESTINATION_TABLE DT2 WHERE DT2.PRIMARY_KEY = ST.PRIMARY_KEY ); 

Si quelqu’un veut lire ma solution pour SQLite dans Cordova, j’ai obtenu cette méthode js générique grâce à @david answer ci-dessus.

 function addOrUpdateRecords(tableName, values, callback) { get_columnNames(tableName, function (data) { var columnNames = data; myDb.transaction(function (transaction) { var query_update = ""; var query_insert = ""; var update_ssortingng = "UPDATE " + tableName + " SET "; var insert_ssortingng = "INSERT INTO " + tableName + " SELECT "; myDb.transaction(function (transaction) { // Data from the array [[data1, ... datan],[()],[()]...]: $.each(values, function (index1, value1) { var sel_str = ""; var upd_str = ""; var remoteid = ""; $.each(value1, function (index2, value2) { if (index2 == 0) remoteid = value2; upd_str = upd_str + columnNames[index2] + "='" + value2 + "', "; sel_str = sel_str + "'" + value2 + "', "; }); sel_str = sel_str.substr(0, sel_str.length - 2); sel_str = sel_str + " WHERE NOT EXISTS(SELECT changes() AS change FROM "+tableName+" WHERE change <> 0);"; upd_str = upd_str.substr(0, upd_str.length - 2); upd_str = upd_str + " WHERE remoteid = '" + remoteid + "';"; query_update = update_string + upd_str; query_insert = insert_string + sel_str; // Start transaction: transaction.executeSql(query_update); transaction.executeSql(query_insert); }); }, function (error) { callback("Error: " + error); }, function () { callback("Success"); }); }); }); } 

Alors, commencez par choisir les noms de colonne avec cette fonction:

 function get_columnNames(tableName, callback) { myDb.transaction(function (transaction) { var query_exec = "SELECT name, sql FROM sqlite_master WHERE type='table' AND name ='" + tableName + "'"; transaction.executeSql(query_exec, [], function (tx, results) { var columnParts = results.rows.item(0).sql.replace(/^[^\(]+\(([^\)]+)\)/g, '$1').split(','); ///// RegEx var columnNames = []; for (i in columnParts) { if (typeof columnParts[i] === 'ssortingng') columnNames.push(columnParts[i].split(" ")[0]); }; callback(columnNames); }); }); } 

Ensuite, générez les transactions par programme.

“Valeurs” est un tableau que vous devez construire avant et il représente les lignes que vous souhaitez insérer ou mettre à jour dans la table.

“remoteid” est l’id que j’ai utilisé comme référence, puisque je suis en train de synchroniser avec mon serveur distant.

Pour l’utilisation du plugin SQLite Cordova, veuillez vous référer au lien officiel

À partir de la version 3.24.0, UPSERT est pris en charge par SQLite.

De la documentation :

UPSERT est une addition de syntaxe spéciale à INSERT qui entraîne le comportement de INSERT en tant que UPDATE ou en tant que no-op si INSERT enfreint une contrainte d’unicité. UPSERT n’est pas un SQL standard. UPSERT dans SQLite suit la syntaxe établie par PostgreSQL. La syntaxe UPSERT a été ajoutée à SQLite avec la version 3.24.0 (en attente).

Un UPSERT est une instruction INSERT ordinaire suivie de la clause spéciale ON CONFLICT

entrer la description de l'image ici

Source de l’image: https://www.sqlite.org/images/syntax/upsert-clause.gif

Vous pouvez en effet faire une mise à jour dans SQLite, cela semble juste un peu différent de celui auquel vous êtes habitué. Cela ressemblerait à quelque chose comme:

 INSERT INTO table name (column1, column2) VALUES ("value12", "value2") WHERE id = 123 ON CONFLICT DO UPDATE SET column1 = "value1", column2 = "value2" WHERE id = 123 

Je pense que cela peut être ce que vous recherchez: clause ON CONFLICT .

Si vous définissez votre tableau comme ceci:

 CREATE TABLE table1( id INTEGER PRIMARY KEY ON CONFLICT REPLACE, field1 TEXT ); 

Maintenant, si vous faites un INSERT avec un identifiant qui existe déjà, SQLite lance automatiquement UPDATE au lieu de INSERT.

Hth …

Cette méthode remixe quelques-unes des autres méthodes pour répondre à cette question et intègre l’utilisation de CTE (Common Table Expressions). Je vais présenter la requête puis expliquer pourquoi j’ai fait ce que j’ai fait.

Je voudrais changer le nom de famille de l’employé 300 en DAVIS s’il y a un employé 300. Sinon, j’appendai un nouvel employé.

Nom de la table: employees Colonnes: id, first_name, last_name

La requête est:

 INSERT OR REPLACE INTO employees (employee_id, first_name, last_name) WITH registered_employees AS ( --CTE for checking if the row exists or not SELECT --this is needed to ensure that the null row comes second * FROM ( SELECT --an existing row * FROM employees WHERE employee_id = '300' UNION SELECT --a dummy row if the original cannot be found NULL AS employee_id, NULL AS first_name, NULL AS last_name ) ORDER BY employee_id IS NULL --we want nulls to be last LIMIT 1 --we only want one row from this statement ) SELECT --this is where you provide defaults for what you would like to insert registered_employees.employee_id, --if this is null the SQLite default will be used COALESCE(registered_employees.first_name, 'SALLY'), 'DAVIS' FROM registered_employees ; 

Fondamentalement, j’ai utilisé le CTE pour réduire le nombre de fois où l’instruction select doit être utilisée pour déterminer les valeurs par défaut. Comme il s’agit d’un CTE, nous sélectionnons simplement les colonnes que nous voulons dans la table et l’instruction INSERT l’utilise.

Maintenant, vous pouvez décider quelles valeurs par défaut vous souhaitez utiliser en remplaçant les valeurs NULL, dans la fonction COALESCE, par ce que les valeurs doivent être.

Suivant Aristote Pagaltzis et l’idée de COALESCE partir de la réponse d’ Eric B , il s’agit d’une option permettant de mettre à jour seulement quelques colonnes ou d’insérer une ligne complète si elle n’existe pas.

Dans ce cas, imaginez que le titre et le contenu soient mis à jour, en conservant les anciennes valeurs lorsqu’elles existent et en insérant celles fournies lorsque le nom n’a pas été trouvé:

NOTE L’ id est forcé à être NULL lorsque INSERT est censé être auto-incrémenté. S’il ne s’agit que d’une clé primaire générée, COALESCE peut également être utilisé (voir le commentaire d’Aristote Pagaltzis ).

 WITH new (id, name, title, content, author) AS ( VALUES(100, 'about', 'About this site', 'Whatever new content here', 42) ) INSERT OR REPLACE INTO page (id, name, title, content, author) SELECT old.id, COALESCE(old.name, new.name), new.title, new.content, COALESCE(old.author, new.author) FROM new LEFT JOIN page AS old ON new.name = old.name; 

Donc, la règle générale serait que, si vous souhaitez conserver les anciennes valeurs, utilisez COALESCE , lorsque vous souhaitez mettre à jour les valeurs, utilisez new.fieldname

Ayant juste lu ce fil et été déçu que ce ne soit pas facile de se contenter de ce “UPSERT”, j’ai enquêté plus avant …

Vous pouvez réellement le faire directement et facilement dans SQLITE.

Au lieu d’utiliser: INSERT INTO

Utilisez: INSERT OR REPLACE INTO

Cela fait exactement ce que vous voulez qu’il fasse!

 SELECT COUNT(*) FROM table1 WHERE id = 1; 

si COUNT(*) = 0

 INSERT INTO table1(col1, col2, cole) VALUES(var1,var2,var3); 

sinon si COUNT(*) > 0

 UPDATE table1 SET col1 = var4, col2 = var5, col3 = var6 WHERE id = 1;