Styl pytań i odpowiedzi
Cóż, po godzinach badań i walki z tym problemem, dowiedziałem się, że są dwa sposoby na osiągnięcie tego, w zależności od struktury twojego stołu i czy masz włączone ograniczenia kluczy obcych, aby zachować integralność. Chciałbym podzielić się tym w przejrzystym formacie, aby zaoszczędzić trochę czasu ludziom, którzy mogą być w mojej sytuacji.
Opcja 1: Możesz sobie pozwolić na usunięcie wiersza
Innymi słowy, nie masz klucza obcego lub jeśli go masz, silnik SQLite jest skonfigurowany tak, aby nie było żadnych wyjątków integralności. Najlepszą drogą jest WSTAWIĆ LUB WYMIENIĆ . Jeśli próbujesz wstawić / zaktualizować odtwarzacz, którego identyfikator już istnieje, silnik SQLite usunie ten wiersz i wstawi podane przez Ciebie dane. Teraz pojawia się pytanie: co zrobić, aby stary identyfikator był powiązany?
Powiedzmy, że chcemy UPSERT z danymi user_name = 'steven' i age = 32.
Spójrz na ten kod:
INSERT INTO players (id, name, age)
VALUES (
coalesce((select id from players where user_name='steven'),
(select max(id) from drawings) + 1),
32)
Sztuczka polega na połączeniu. Zwraca identyfikator użytkownika „steven”, jeśli taki istnieje, w przeciwnym razie zwraca nowy, świeży identyfikator.
Opcja 2: Nie możesz sobie pozwolić na usunięcie wiersza
Po małpowaniu z poprzednim rozwiązaniem zdałem sobie sprawę, że w moim przypadku może to skończyć się zniszczeniem danych, ponieważ ten identyfikator działa jako klucz obcy dla innej tabeli. Poza tym stworzyłem tabelę z klauzulą ON DELETE CASCADE , co oznaczałoby, że po cichu usuwałoby dane. Niebezpieczny.
Tak więc najpierw pomyślałem o klauzuli IF, ale SQLite ma tylko CASE . A tego CASE nie można użyć (a przynajmniej mi się to nie udało) do wykonania jednej AKTUALIZACJI zapytania jeśli ISTNIEJE (wybierz identyfikator z graczy, gdzie nazwa_użytkownika = 'steven') i WSTAWIĆ, jeśli nie. Nie idź.
A potem, w końcu, z powodzeniem użyłem brutalnej siły. Logika jest taka, że dla każdego UPSERT , który chcesz wykonać, najpierw wykonaj INSERT LUB IGNORUJ aby upewnić się, że jest wiersz z naszym użytkownikiem, a następnie wykonaj zapytanie UPDATE z dokładnie tymi samymi danymi, które próbujesz wstawić.
Te same dane co poprzednio: user_name = 'steven' i age = 32.
-- make sure it exists
INSERT OR IGNORE INTO players (user_name, age) VALUES ('steven', 32);
-- make sure it has the right data
UPDATE players SET user_name='steven', age=32 WHERE user_name='steven';
I to wszystko!
EDYTOWAĆ
Jak skomentował Andy, próba wstawienia najpierw, a następnie aktualizacji może prowadzić do wyzwalania wyzwalaczy częściej niż oczekiwano. Nie jest to moim zdaniem kwestia bezpieczeństwa danych, ale prawdą jest, że odpalanie niepotrzebnych zdarzeń ma niewielki sens. Dlatego ulepszonym rozwiązaniem byłoby:
-- Try to update any existing row
UPDATE players SET age=32 WHERE user_name='steven';
-- Make sure it exists
INSERT OR IGNORE INTO players (user_name, age) VALUES ('steven', 32);