CODEWITHSUNDEEP
sqliteconstraintssql-insert
user2780638
user2780638

Reputation: 2703

"Insert if not exists" statement in SQLite

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.

Upvotes: 256

Views: 294084

Answers (5)

Jim Beem
Jim Beem

Reputation: 31

For me this worked for non unique columns (here "Name"):

CREATE TABLE InsertDemo ( ID INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT, Name TEXT )
    
INSERT OR IGNORE INTO InsertDemo (ID, Name)    -- insert first row
    VALUES ( ( SELECT ID FROM InsertDemo WHERE Name = 'Miller' ), 'Miller' );    
INSERT OR IGNORE INTO InsertDemo (ID, Name)    -- but no second
    VALUES ( ( SELECT ID FROM InsertDemo WHERE Name = 'Miller' ), 'Miller' );
        
SELECT Count(ID) FROM InsertDemo;   -- returns 1

Upvotes: 0

Ali Bagheri
Ali Bagheri

Reputation: 3419

For a unique column, use this:

INSERT OR REPLACE INTO tableName (...) values(...);

For more information, see: sqlite.org/lang_insert

Upvotes: 58

Cyclonecode
Cyclonecode

Reputation: 30001

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.

Upvotes: 239

suat dmk
suat dmk

Reputation: 126

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

Upvotes: 7

CL.
CL.

Reputation: 180040

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)

Upvotes: 637

Related Questions