SQLite - UPSERT * non * insérer ou remplacer

Question

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

Insérez mise à Jour de procédures stockées sous SQL Server

Est-il une manière intelligente de faire cela dans SQLite, que je n'ai pas pensé?

Fondamentalement, je veux mettre à jour trois des quatre colonnes si l'enregistrement existe, Si elle n'existe pas je veux INSÉRER l'enregistrement avec la valeur par défaut (NUL) de la valeur pour la quatrième colonne.

L'ID est une clé primaire, donc il n'y aura que jamais être un enregistrement de UPSERT.

(J'essaie d'éviter la surcharge de sélection afin de déterminer si j'ai besoin de mettre à JOUR ou INSÉRER évidemment)

Des Suggestions?

Answer 1

En supposant que 3 colonnes dans la table.. ID, NOM, RÔLE

MAUVAIS: Cela permettra d'insérer ou remplacer toutes les colonnes avec les nouvelles valeurs pour l'ID=1:

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

MAUVAIS: Cela permettra d'insérer ou remplacer les 2 colonnes... le NOM de la colonne est définie à NULL ou à la valeur par défaut:

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

BON: la mise à 2 colonnes. Lorsque ID=1 existe, le NOM ne sera pas affectée. Lorsque ID=1 n'existe pas, le nom par défaut (NULL).

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

Ceci mettra à jour 2 colonnes. Lorsque ID=1 existe, le RÔLE ne sera pas affectée. Lorsque ID=1 n'existe pas, le rôle sera réglé à "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')
          );

Answer 2

INSÉRER ou remplacer n’est pas équivalent à « UPSERT ».

Dire que j’ai la table Employee avec les champs id, nom et rôle :

Boom, vous avez perdu le nom du matricule 1. SQLite 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.

Answer 3

Eric B, la réponse est OK si vous souhaitez conserver un ou peut-être deux colonnes de la ligne existante. Si vous voulez préserver beaucoup de lignes, ça devient trop lourd rapide.

Voici une approche qui va à l'échelle de n'importe quel montant 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 ligne – id est utilisé uniquement pour les clés étrangères, de sorte que le point est pour SQLite pour choisir 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 basée sur sa name, je veux continuer à avoir de l'ancienne valeur de l'ID (évidemment!).

J'ai réaliser un vrai UPSERT à la suite de construire:

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

Ici, si une ligne n'existaient pas précédemment old.id sera NULLE et SQLite sera alors attribuer automatiquement un ID, mais si il y a déjà était telle ligne, old.id ont une valeur réelle et ce sera réutilisé. Ce qui est exactement ce que je voulais.

En fait, c'est très souple. Notez comment l' ts colonne est complètement absent sur tous les côtés – parce qu'il a un DEFAULT de la valeur, SQLite va juste faire la bonne chose en tout cas, si je n'ai pas à prendre soin de moi-même.

Vous pouvez également inclure une colonne sur deux l' new et old côtés puis utiliser, par exemple, COALESCE(new.content, old.content) dans l'avant - SELECT - à-dire "insérer le nouveau contenu s'il y avait de tout, sinon conserver l'ancien contenu" – par exemple, si vous utilisez une requête fixe et sont obligatoires pour les nouvelles valeurs de remplacement.

Answer 4

Si vous sont généralement de faire des mises à jour, je voudrais ..

1. Commencer une transaction
2. Faire la mise à jour
3. Vérifier le nombre de lignes
4. Si c'est 0 ne l'insert
5. S'engager

Si vous êtes, en général, comme je l'insère

1. Commencer une transaction
2. Essayez un insert
3. Vérifier la clé primaire d'erreur de violation d'
4. si nous avons obtenu une erreur de faire la mise à jour
5. S'engager

De cette façon, vous évitez de le sélectionner et vous êtes un point de vue transactionnel son sur Sqlite.

Answer 5

Je me rends compte que c'est un vieux thread mais j'ai travaillé dans sqlite3 comme de la fin et est venu avec cette méthode qui mieux adapté à mes besoins de générer dynamiquement des 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>; 

C'est toujours 2 requêtes avec une clause where sur la mise à jour, mais semble faire l'affaire. J'ai également cette vision dans ma tête que sqlite peut optimiser loin de la mise à jour de déclaration tout à fait si l'appel à des changements() est supérieure à zéro. Si oui ou non la réalité qui est au-delà de mes connaissances, mais un homme peut rêver, ne peut-il pas? ;)

Pour des points bonus, vous pouvez ajouter cette ligne qui vous renvoie l'id de la ligne que ce soit une nouvelle ligne ou une ligne existante.

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