Question 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, S'il n'existe pas, je veux INSÉRER l'enregistrement avec la valeur par défaut (NUL) pour la quatrième colonne.

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

(J'essaie d'éviter le surdébit de SELECT afin de déterminer si j'ai besoin de METTRE À JOUR ou INSÉRER é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 Donc, je pense que Blob1 et Blob2 ne seront pas remplacés (comme vous le souhaitez)


UPDATEs dans SQLite lorsque les données de liaison sont une transaction complète, ce qui signifie Chaque ligne envoyée à mettre à jour nécessite: Préparer / Relier / Etape / Finaliser les instructions contrairement à l'INSERT qui permet l'utilisation de la fonction de réinitialisation

La vie d'un objet d'instruction va quelque chose comme ceci:

  1. Créez l'objet en utilisant sqlite3_prepare_v2 ()
  2. Lier les valeurs pour héberger les paramètres à 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'objet d'instruction en utilisant 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 le select pour lire la 4ème colonne (Blob3) et ensuite utiliser REPLACE pour écrire un nouvel enregistrement mélangeant la 4ème colonne originale avec les nouvelles données pour les 3 premières colonnes?


451
2018-01-07 01:47


origine


Réponses:


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


MAL: 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');

MAL: 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');

BIEN: 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')
          );

761
2017-12-02 00:55



INSERT OU REPLACE est NE PAS équivalent à "UPSERT".

Dites 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.


116
2017-11-23 07:46



La réponse d'Eric B est OK si vous souhaitez conserver seulement une ou peut-être deux colonnes de la ligne existante. Si vous voulez conserver beaucoup de colonnes, cela devient trop lourd.

Voici une approche qui s'adaptera bien à n'importe quelle quantité de colonnes de chaque côté. Pour l'illustrer, je vais supposer 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 la rangée - id est utilisé uniquement pour les clés étrangères, il est donc nécessaire que SQLite sélectionne la valeur ID elle-même lors de l'insertion d'une nouvelle ligne. Mais lors de la mise à jour d'une ligne existante en fonction de ses name, Je veux qu'il continue à avoir l'ancienne valeur d'identification (é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 assignera alors automatiquement un identifiant, mais s'il y avait déjà une telle ligne, old.id aura une valeur réelle et cela sera réutilisé. Ce qui est exactement ce que je voulais.

En fait, c'est très flexible. Notez comment le ts colonne est complètement manquant de tous les côtés - parce qu'il a un DEFAULT valeur, SQLite fera juste la bonne chose dans tous les cas, donc je n'ai pas à m'en occuper moi-même.

Vous pouvez également inclure une colonne à la fois sur le new et old côtés, puis utilisez par ex. COALESCE(new.content, old.content) dans l'extérieur SELECT dire "insérer le nouveau contenu s'il y en avait, sinon conserver l'ancien contenu" - par ex. Si vous utilisez une requête fixe et que vous liez les nouvelles valeurs avec des espaces réservés.


99
2017-09-22 08:13



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 faire l'insertion
  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 eu une erreur, faites la mise à jour
  5. Commettre

De cette façon vous évitez le select et vous êtes transactionnel sur Sqlite.


73
2018-01-07 02:29



Je réalise qu'il s'agit d'un ancien thread mais j'ai récemment travaillé sur sqlite3 et j'ai trouvé cette méthode qui convenait le mieux à mes besoins de génération dynamique de requêtes paramétrées:

insert or ignore into <table>(<primaryKey>, <column1>, <column2>, ...) values(<primaryKeyValue>, <value1>, <value2>, ...); 
update <table> set <column1>=<value1>, <column2>=<value2>, ... where changes()=0 and <primaryKey>=<primaryKeyValue>; 

Il reste 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 update si l'appel à changes () 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 ajouter 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 <primaryKeyValue> end;

56
2017-09-08 19:12



2018-05-18 STOP PRESSE.

Support 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 fait que l'INSERT se comporte comme un UPDATE ou un no-op si l'INSERT viole une contrainte d'unicité. UPSERT n'est pas un SQL standard. UPSERT dans SQLite suit la syntaxe établie par PostgreSQL.

enter image description here

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 essaie de mettre à jour, si l'enregistrement est là alors changes () == 1 donc l'insert n'est pas action-ed.

alternativement:

Une autre façon complètement différente de faire ceci est: Dans mon application j'ai mis mon rowID dans la mémoire pour être long.MaxValue quand je crée la rangée dans la mémoire. (MaxValue ne sera jamais utilisé comme identifiant que vous ne pourrez pas vivre assez longtemps ... Alors, si rowID n'est pas cette valeur, il doit déjà figurer dans la base de données, il a donc besoin d'un insert. Ceci n'est utile que si vous pouvez suivre les rowIDs dans votre application.


41
2017-08-28 12:00



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 ID existe.
2. Si la mise à jour n'a modifié aucune ligne (NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0)), puis insérez le dossier.

Ainsi, soit un enregistrement existant a été mis à jour, soit une insertion sera effectuée.

Le détail important est d'utiliser la fonction SQL de changes () pour vérifier si l'instruction de mise à jour frappe des enregistrements existants et n'effectue l'instruction d'insertion que si elle n'a pas atteint un enregistrement.

Une chose à mentionner est que la fonction changes () ne retourne pas les changements effectués par les 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;

13
2018-02-27 22:47



Expansion sur La réponse d'Aristote vous pouvez SELECT à partir d'une table 'singleton' factice (une table de votre propre création avec une seule ligne). Cela évite une duplication.

J'ai également conservé l'exemple portable sur MySQL et SQLite et j'ai 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";

5
2018-05-21 02:07