Znalazłem kilka „odpowiednich” rozwiązań dla klasycznego „Jak wstawić nowy rekord lub zaktualizować go, jeśli już istnieje”, ale nie mogę zmusić żadnego z nich do pracy w SQLite.

Mam tabelę zdefiniowaną w następujący sposób:

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

Chcę dodać rekord z unikalną nazwą. Jeśli nazwa już istnieje, chcę zmodyfikować pola.

Czy ktoś może mi powiedzieć, jak to zrobić, proszę?

Zajrzyj na http://sqlite.org/lang_conflict.html .

Chcesz coś takiego:

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

Zauważ, że każde pole nie znajdujące się na liście wstawiania zostanie ustawione na NULL, jeśli wiersz już istnieje w tabeli. Oto dlaczego dla tej IDkolumny jest podselekcja : W przypadku zamiany instrukcja ustawi wartość NULL, a następnie zostanie przydzielony nowy identyfikator.

Tego podejścia można również użyć, jeśli chcesz pozostawić określone wartości pól w spokoju, jeśli wiersz w przypadku zamiany jest ustawiony, ale ustaw pole na NULL w przypadku wstawiania.

Na przykład zakładając, że chcesz odejść w Seenspokoju:

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"));

Należy użyć INSERT OR IGNOREpolecenia, a następnie UPDATEpolecenia: W poniższym przykładzie namejest kluczem podstawowym:

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

Pierwsze polecenie wstawi rekord. Jeśli rekord istnieje, zignoruje błąd spowodowany konfliktem z istniejącym kluczem podstawowym.

Drugie polecenie zaktualizuje rekord (który teraz zdecydowanie istnieje)

Musisz ustawić ograniczenie dla tabeli, aby wywołać „ konflikt ”, który następnie rozwiązujesz, wykonując zamianę:

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);

Następnie możesz wydać:

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);

„WYBIERZ * Z danych” da ci:

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

Zauważ, że data.id to „3”, a nie „1”, ponieważ REPLACE wykonuje DELETE i INSERT, a nie UPDATE. Oznacza to również, że musisz upewnić się, że zdefiniujesz wszystkie niezbędne kolumny, w przeciwnym razie otrzymasz nieoczekiwane wartości NULL.

Najpierw zaktualizuj. Jeśli zmieniona liczba wierszy ma wartość 0, wstaw ją. Jest to najłatwiejszy i odpowiedni dla wszystkich RDBMS .

INSERT OR REPLACE zastąpi pozostałe pola wartością domyślną.

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

Jeśli chcesz zachować drugie pole

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

lub używając UPSERT (składnia została dodana do SQLite w wersji 3.24.0 (2018-06-04))

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

W excluded.prefiks równa wartości VALUES.

Upsert jest tym, czego chcesz. UPSERTdo SQLite dodano składnię w wersji 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;

Ostrzegamy, że w tym momencie rzeczywiste słowo „UPSERT” nie jest częścią składni upsert.

Prawidłowa składnia to

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

a jeśli robisz, INSERT INTO SELECT ...select musi przynajmniej WHERE truerozwiązać dwuznaczność parsera na temat tokena ONze składnią łączenia.

Ostrzegamy, że INSERT OR REPLACE...usunie rekord przed wstawieniem nowego, jeśli trzeba go wymienić, co może być złe, jeśli masz kaskady kluczy obcych lub inne wyzwalacze usuwania.

Jeśli nie masz klucza podstawowego, możesz wstawić, jeśli nie istnieje, a następnie wykonać aktualizację. Tabela musi zawierać co najmniej jeden wpis przed użyciem tego.

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';

Wierzę, że chcesz UPSERT .

„WSTAW LUB WYMIEŃ” bez dodatkowej sztuczki w tej odpowiedzi zresetuje wszystkie pola, których nie określisz, na NULL lub inną wartość domyślną. (To zachowanie INSERT OR REPLACE nie różni się od UPDATE; jest dokładnie takie jak INSERT, ponieważ tak naprawdę jest INSERT; jednak jeśli chciałeś UPDATE-jeśli-istnieje, prawdopodobnie chcesz semantyki UPDATE i będzie nieprzyjemnie zaskoczony faktycznym wynikiem.)

Podstępem wynikającym z sugerowanej implementacji UPSERT jest w zasadzie użycie INSERT OR REPLACE, ale określenie wszystkich pól za pomocą wbudowanych klauzul SELECT w celu pobrania bieżącej wartości dla pól, których nie chcesz zmieniać.

Myślę, że warto zauważyć, że może wystąpić tutaj nieoczekiwane zachowanie, jeśli nie do końca rozumiesz, w jaki sposób KLUCZ PODSTAWOWY i UNIKALNY oddziałują na siebie.

Na przykład, jeśli chcesz wstawić rekord tylko wtedy, gdy pole NAME nie jest aktualnie zajęte, a jeśli tak, chcesz, aby zadziałał wyjątek ograniczenia, wówczas WSTAW LUB WYMIENI nie rzuci i wyjątek, a zamiast tego będzie rozwiązać samo ograniczenie UNIKALNE , zastępując konflikt rekordu (istniejący rekord tą samą NAZWĄ ). Gaspard's pokazuje to bardzo dobrze w swojej powyższej odpowiedzi .

Jeśli chcesz, aby zadziałał wyjątek ograniczenia, musisz użyć instrukcji INSERT i polegać na osobnej komendzie UPDATE, aby zaktualizować rekord, gdy dowiesz się, że nazwa nie została przyjęta.