Insert if not exists statement in SQLite

Question

I have an SQLite database  I am trying to insert values  users id  lessoninfo id  in table bookmarks  only if both do not exist before in a row   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        This gives an error saying      db error near where syntax

User · Accepted Answer

If you have a table called memos that has two columns id and text you should be able to do like this:

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

If a record already contains a row where text is equal to 'text to insert' and id is equal to 5, then the insert operation will be ignored.

I don't know if this will work for your particular query, but perhaps it give you a hint on how to proceed.

I would advice that you instead design your table so that no duplicates are allowed as explained in @CLs answer below.

User · Answer

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    This is the fastest way   For some other SQL engines  you can use a Dummy table containing 1 record  e g   select 1  167 from ONE RECORD DUMMY TABLE

User · Answer

For a unique column  use this   INSERT OR REPLACE INTO table    values      For more information  see  sqlite org lang insert

User · Answer

If you never want to have duplicates  you should declare this as a table constraint   CREATE TABLE bookmarks      users id INTEGER      lessoninfo id INTEGER      UNIQUE users id  lessoninfo id        A primary key over both columns would have the same effect    It is then possible to tell the database that you want to silently ignore records that would violate such a constraint   INSERT OR IGNORE INTO bookmarks users id  lessoninfo id  VALUES 123  456

[sqlite] "Insert if not exists" statement in SQLite

Examples related to sqlite

Examples related to constraints

Examples related to sql-insert