INSERT IF NOT EXISTS ELSE UPDATE? (Programare, Sqlite, Insert, Există, Upsert, Îmbinarea Rezolvarea Conflictelor)

SparkyNZ a intrebat.

Am găsit câteva soluții „ar fi” pentru clasica întrebare „Cum introduc o nouă înregistrare sau actualizez una dacă există deja”, dar nu pot să fac niciuna dintre ele să funcționeze în SQLite.

Am un tabel definit după cum urmează:

CREATE TABLE Book 
ID     INTEGER PRIMARY KEY AUTOINCREMENT,
Name   VARCHAR(60) UNIQUE,
TypeID INTEGER,
Level  INTEGER,
Seen   INTEGER

Ceea ce vreau să fac este să adaug o înregistrare cu un nume unic. Dacă numele există deja, vreau să modific câmpurile.

Îmi poate spune cineva cum să fac acest lucru, vă rog?

Comentarii

  • „insert or replace” este complet diferit de „inserare sau actualizare” –  > Por Fattie.
  • Ce zici de UPSERT? –  > Por ashleedawg.
9 răspunsuri
janm

Aruncați o privire la http://sqlite.org/lang_conflict.html.

Vrei ceva de genul:

insert or replace into Book (ID, Name, TypeID, Level, Seen) values
((select ID from Book where Name = "SearchName"), "SearchName", ...);

Rețineți că orice câmp care nu se află în lista de inserare va fi setat la NULL dacă rândul există deja în tabel. Acesta este motivul pentru care există o subsecțiune pentru câmpul ID coloană: În cazul înlocuirii, instrucțiunea ar seta-o la NULL și apoi ar fi alocat un nou ID.

Această abordare poate fi utilizată și în cazul în care doriți să lăsați anumite valori de câmp în cazul rândului în cazul înlocuirii, dar să setați câmpul la NULL în cazul inserării.

De exemplu, presupunând că doriți să lăsați Seen să nu fie afectate:

insert or replace into Book (ID, Name, TypeID, Level, Seen) values (
   (select ID from Book where Name = "SearchName"),
   "SearchName",
    5,
    6,
    (select Seen from Book where Name = "SearchName"));

Comentarii

    117

  • Greșit „insert or replace” este diferit de „insert or update”. Pentru un răspuns valabil, consultați stackoverflow.com/questions/418898/… –  > Por rds.
  • @rds Nu, nu este greșit, deoarece această întrebare spune „modifică câmpurile”, iar cheia primară nu face parte din lista de coloane, dar toate celelalte câmpuri sunt. Dacă veți avea cazuri de colț în care nu înlocuiți toate valorile câmpului sau dacă vă jucați cu cheia primară, ar trebui să faceți ceva diferit. Dacă aveți un set complet de câmpuri noi, această abordare este foarte bună. Aveți o problemă specifică pe care nu o văd? –  > Por janm.
  • Este valabilă dacă știți toate valorile noi pentru toate câmpurile. Dacă utilizatorul actualizează doar, să zicem, valoarea Level, , această abordare nu poate fi urmată. –  > Por rds.
  • Corect. Celălalt răspuns este relevant, dar această abordare este valabilă pentru actualizarea tuturor câmpurilor (cu excepția cheii). –  > Por Ярослав Рахматуллин.
  • Da Acest răspuns este complet greșit. Ce se va întâmpla dacă vreau doar să actualizez valoarea unei singure coloane pe Conflict de la cea nouă. În cazul de mai sus, toate celelalte date vor fi înlocuite cu cele noi, ceea ce nu este corect. –  > Por Mrug.
moshik

Ar trebui să utilizați INSERT OR IGNORE urmată de o comandă UPDATE comandă:În următorul exemplu name este o cheie primară:

INSERT OR IGNORE INTO my_table (name, age) VALUES ('Karen', 34)
UPDATE my_table SET age = 34 WHERE name='Karen'

Prima comandă va introduce înregistrarea. Dacă înregistrarea există, se va ignora eroarea cauzată de conflictul cu o cheie primară existentă.

A doua comandă va actualiza înregistrarea (care acum există cu siguranță)

Comentarii

  • în ce moment va ignora? atunci când numele și vârsta sunt ambele identice? –  > Por Arnaud Aliès.
  • Aceasta ar trebui să fie soluția… dacă folosiți orice trigger la inserare, răspunsul acceptat se declanșează de fiecare dată. Acesta nu și efectuează doar o actualizare –  > Por PodTech.io.
  • Ignoră pe baza exclusivă a numelui. Amintiți-vă că numai coloana „name” este o cheie primară. –  > Por Gabriel Ferrer.
  • Când înregistrarea este nouă, atunci actualizarea nu este necesară, dar va fi executată oricum, ceea ce duce la performanțe proaste? –  > Por MarcG.
  • Cum se execută o instrucțiune pregătită pentru acest lucru? –  > Por prashant.
Lily B

Trebuie să setați o constrângere pe tabel pentru a declanșa o „conflict” pe care apoi îl rezolvați prin înlocuire:

CREATE TABLE data   (id INTEGER PRIMARY KEY, event_id INTEGER, track_id INTEGER, value REAL);
CREATE UNIQUE INDEX data_idx ON data(event_id, track_id);

Apoi puteți emite:

INSERT OR REPLACE INTO data VALUES (NULL, 1, 2, 3);
INSERT OR REPLACE INTO data VALUES (NULL, 2, 2, 3);
INSERT OR REPLACE INTO data VALUES (NULL, 1, 2, 5);

„SELECT * FROM data” vă va da:

2|2|2|3.0
3|1|2|5.0

Rețineți că data.id este „3” și nu „1”, deoarece REPLACE efectuează o ȘTERGERE și un INSERT, nu un UPDATE. Acest lucru înseamnă, de asemenea, că trebuie să vă asigurați că definiți toate coloanele necesare, altfel veți obține valori NULL neașteptate.

Burçin

În primul rând, actualizați-o. Dacă numărul de rânduri afectate = 0, atunci introduceți-l. Este cel mai simplu și potrivit pentru toți RDBMS.

Comentarii

  • Două operații nu ar trebui să fie o problemă cu o tranzacție la nivelul de izolare adecvat, indiferent de baza de date. –  > Por janm.
  • Insert or Replace este într-adevăr mai preferabil. –  > Por MPelletier.
  • Mi-aș dori cu adevărat ca documentația IT să conțină mai multe exemple. Am încercat acest lucru de mai jos și nu funcționează (sintaxa mea este evident greșită). Aveți vreo idee despre cum ar trebui să fie? INSERT INTO Book (Name,TypeID,Level,Seen) VALUES( ‘Superman’, ‘2’, ’14’, ‘0’ ) ON CONFLICT REPLACE Book (Name,TypeID,Level,Seen) VALUES( ‘Superman’, ‘2’, ‘2’, ’14’, ‘0’ ) – –  > Por SparkyNZ.
  • De asemenea, ce se întâmplă dacă valorile rândurilor sunt exact aceleași, atunci numărul de rânduri afectate va fi zero și va fi creat un nou rând duplicat. –  > Por barkside.
  • +1, deoarece INSERAȚI SAU ÎNLOCUIȚI va șterge rândul original în caz de conflict, iar dacă nu setați toate coloanele, veți pierde valorile originale –  > Por Pavel Machyniak.
Aripa de oțel

INSERT OR REPLACE va înlocui celelalte câmpuri la valoarea implicită.

sqlite> CREATE TABLE Book (
  ID     INTEGER PRIMARY KEY AUTOINCREMENT,
  Name   TEXT,
  TypeID INTEGER,
  Level  INTEGER,
  Seen   INTEGER
);

sqlite> INSERT INTO Book VALUES (1001, 'C++', 10, 10, 0);
sqlite> SELECT * FROM Book;
1001|C++|10|10|0

sqlite> INSERT OR REPLACE INTO Book(ID, Name) VALUES(1001, 'SQLite');

sqlite> SELECT * FROM Book;
1001|SQLite|||

Dacă doriți să păstrați celălalt câmp

  • Metoda 1
sqlite> SELECT * FROM Book;
1001|C++|10|10|0

sqlite> INSERT OR IGNORE INTO Book(ID) VALUES(1001);
sqlite> UPDATE Book SET Name='SQLite' WHERE ID=1001;

sqlite> SELECT * FROM Book;
1001|SQLite|10|10|0
  • Metoda 2

Folosind UPSERT (sintaxa a fost adăugată în SQLite cu versiunea 3.24.0 (2018-06-04))

INSERT INTO Book (ID, Name)
  VALUES (1001, 'SQLite')
  ON CONFLICT (ID) DO
  UPDATE SET Name=excluded.Name;

Adresa excluded. egal cu valoarea din prefixul VALUES ('SQLite').

ComradeJoecool

Upsert este ceea ce doriți. UPSERT sintaxa a fost adăugată la SQLite cu versiunea 3.24.0 (2018-06-04).

CREATE TABLE phonebook2(
  name TEXT PRIMARY KEY,
  phonenumber TEXT,
  validDate DATE
);

INSERT INTO phonebook2(name,phonenumber,validDate)
  VALUES('Alice','704-555-1212','2018-05-08')
  ON CONFLICT(name) DO UPDATE SET
    phonenumber=excluded.phonenumber,
    validDate=excluded.validDate
  WHERE excluded.validDate>phonebook2.validDate;

Fiți atenți că în acest moment cuvântul real „UPSERT” nu face parte din sintaxa upsert.

Sintaxa corectă este

INSERT INTO ... ON CONFLICT(...) DO UPDATE SET...

iar dacă faceți INSERT INTO SELECT ... selectul dvs. are nevoie de cel puțin WHERE true pentru a rezolva ambiguitatea parserului cu privire la token-ul ON cu sintaxa join.

Vă avertizăm că INSERT OR REPLACE... va șterge înregistrarea înainte de a insera una nouă dacă trebuie să o înlocuiască, ceea ce ar putea fi rău dacă aveți cascade de chei străine sau alte declanșatoare de ștergere.

Comentarii

  • Există în documentație și eu am ultima versiune de sqlite, care este 3.25.1, dar nu funcționează pentru mine.  > Por Bentaiba Miled Basma.
  • ați încercat textual cele două interogări de mai sus? –  > Por TovarășulJoecool.
  • Rețineți că Ubuntu 18.04 vine cu SQLite3 v3.22, , nu 3.25, deci nu suportă UPSERT sintaxa. –  > Por starbeamrainbowlabs.
  • Vă mulțumim pentru versiunea de referință privind caracteristica! –  > Por Matteo.
matt

Dacă nu aveți o cheie primară, Puteți insera dacă nu există, apoi faceți o actualizare. Tabelul trebuie să conțină cel puțin o intrare înainte de a utiliza acest lucru.

INSERT INTO Test 
   (id, name)
   SELECT 
      101 as id, 
      'Bob' as name
   FROM Test
       WHERE NOT EXISTS(SELECT * FROM Test WHERE id = 101 and name = 'Bob') LIMIT 1;

Update Test SET id='101' WHERE name='Bob';

Comentarii

  • Aceasta este singura soluție care a funcționat pentru mine fără a crea intrări duplicate. Probabil pentru că tabelul pe care îl folosesc nu are chei primare și are 2 coloane fără valori implicite. Chiar dacă este o soluție puțin cam lungă, aceasta își face treaba în mod corespunzător și funcționează așa cum era de așteptat. –  > Por Inbar Rose.
metamatt

Cred că doriți UPSERT.

„INSERT OR REPLACE” fără trucurile suplimentare din acel răspuns va reseta toate câmpurile pe care nu le specificați la NULL sau altă valoare implicită. (Acest comportament al INSERT OR REPLACE nu seamănă cu UPDATE; este exact ca INSERT, pentru că de fapt este INSERT; totuși, dacă ceea ce ați dorit este UPDATE-if-exists, probabil că doriți semantica UPDATE și veți fi neplăcut surprins de rezultatul real).

Șmecheria din implementarea UPSERT sugerată este, în principiu, să folosiți INSERT OR REPLACE, dar să specificați toate câmpurile, folosind clauze SELECT încorporate pentru a prelua valoarea curentă pentru câmpurile pe care nu doriți să le modificați.

Matt Matthias

Cred că merită să subliniez faptul că poate exista un comportament neașteptat aici dacă nu înțelegeți bine cum se face PRIMARY KEY și UNIQUE interacționează.

De exemplu, dacă doriți să inserați o înregistrare numai în cazul în care NAME nu este ocupat în prezent, iar dacă este ocupat, doriți să se declanșeze o excepție de constrângere pentru a vă anunța, atunci INSERAȚI SAU ÎNLOCUIȚI nu va arunca o excepție și, în schimb, va rezolva problema UNIQUE prin înlocuirea înregistrării conflictuale (înregistrarea existentă cu același NUME). Gaspard demonstrează foarte bine acest lucru în răspunsul său de mai sus.

Dacă doriți ca o excepție de constrângere să se declanșeze, trebuie să folosiți o restricție de tip INSERT și să vă bazați pe o instrucțiune separată UPDATE separată pentru a actualiza înregistrarea odată ce știți că numele nu este ocupat.