Instrukcja „Wstaw, jeśli nie istnieje” w programie SQLite

143

Mam bazę danych SQLite. Próbuję wstawić wartości ( users_id, lessoninfo_id) do tabeli bookmarks, tylko jeśli obie nie istnieją wcześniej w wierszu.

INSERT INTO bookmarks(users_id,lessoninfo_id) 
VALUES(
    (SELECT _id FROM Users WHERE User='"+$('#user_lesson').html()+"'),
        (SELECT _id FROM lessoninfo 
        WHERE Lesson="+lesson_no+" AND cast(starttime AS int)="+Math.floor(result_set.rows.item(markerCount-1).starttime)+") 
        WHERE NOT EXISTS (
            SELECT users_id,lessoninfo_id from bookmarks 
            WHERE users_id=(SELECT _id FROM Users 
            WHERE User='"+$('#user_lesson').html()+"') AND lessoninfo_id=(
                SELECT _id FROM lessoninfo
                WHERE Lesson="+lesson_no+")))

To daje błąd mówiąc:

błąd db w pobliżu składni gdzie.

sqlite constraints sql-insert

— user2780638
źródło

138

Jeśli masz tabelę o nazwie notatki, która ma dwie kolumny idi textpowinieneś móc to zrobić:

INSERT INTO memos(id,text) 
SELECT 5, 'text to insert' 
WHERE NOT EXISTS(SELECT 1 FROM memos WHERE id = 5 AND text = 'text to insert');

Jeśli rekord zawiera już wiersz, w którym textjest równe „tekst do wstawienia” iid równe 5, operacja wstawiania zostanie zignorowana.

Nie wiem, czy to zadziała w przypadku twojego konkretnego zapytania, ale być może da ci wskazówkę, jak postępować.

Radziłbym zamiast tego zaprojektować swój stół tak, aby żadne duplikaty nie były dozwolone, jak wyjaśniono @CLs answerponiżej.

— Cyclonecode
źródło

436

Jeśli nigdy nie chcesz mieć duplikatów, powinieneś zadeklarować to jako ograniczenie tabeli:

CREATE TABLE bookmarks(
    users_id INTEGER,
    lessoninfo_id INTEGER,
    UNIQUE(users_id, lessoninfo_id)
);

(Klucz podstawowy w obu kolumnach miałby ten sam efekt).

Można wtedy powiedzieć bazie danych, że chcesz po cichu ignorować rekordy, które naruszałyby takie ograniczenie :

INSERT OR IGNORE INTO bookmarks(users_id, lessoninfo_id) VALUES(123, 456)

— CL.
źródło

12

Jaki jest optymalny sposób, aby uzyskać identyfikator nowo wstawionego wiersza lub już istniejącego, aby móc go wstawić do innej tabeli, która ma klucz obcy do tej? np. mam dwa stoły person (id, name unique)i cat (person_id, name unique)chcę wstawić dużo par (person_name, cat_name)?

— Aur Saraf

2

Odczyt identyfikatora istniejącego wiersza jest możliwy tylko za pomocą zapytania SELECT. Ale to jest inne pytanie.

— CL.

1

Być może dodanie ON CONFLICT IGNOREdo CREATE TABLEbyłoby trochę bardziej przydatne

— defhlt

1

@ rightaway717 „Ignoruj” oznacza, że nic się nie dzieje; to pytanie nie ma nic wspólnego z aktualizacją.

— CL.

1

Funkcja insert () @ Hack06 w systemie Android zachowuje się inaczej; powinieneś zamienić go na insertOrThrow () lub insertWithOnConflict () .

— CL.

21

W przypadku unikalnej kolumny użyj tego:

INSERT OR REPLACE INTO table () values();

Aby uzyskać więcej informacji, zobacz: sqlite.org/lang_insert

— Ali Bagheri
źródło

To nie działa dla mnie. zawsze wstawia wartości ('1', '2') do mojej_tabeli (kol1, kol2) jak przy wstawianiu lub zamienianiu; doda wiele wierszy 1 2

— Peter Moore

Mógłbym dodać, że działa to dobrze, jeśli ograniczenie jest wprowadzone Unique(col1,col2)i masz również col3, ponieważ wstawianie lub ignorowanie nie powiedzie się, gdy to zaktualizuje col3 do nowej wartości. insert or replace into my_table values('1','2','5')zastępuje rząd'1','2','3'

— Peter Moore

4

insert into bookmarks (users_id, lessoninfo_id)

select 1, 167
EXCEPT
select user_id, lessoninfo_id
from bookmarks
where user_id=1
and lessoninfo_id=167;

To najszybszy sposób.

W przypadku niektórych innych silników SQL można użyć tabeli Dummy zawierającej 1 rekord. na przykład:

select 1, 167 from ONE_RECORD_DUMMY_TABLE

— suat dmk
źródło