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

Ormuz

Answer 1

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.

Gaullist

Answer 2

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)

Nodule

What is the optimal way to then get the ID of the newly inserted row or the already existing one, so I can insert it to another table that has a foreign key to this one? e.g. I have two tables person (id, name unique) and cat (person_id, name unique) and I want to insert lots of pairs (person_name, cat_name)? – Eke

Reading the ID of an existing row is possible only with a SELECT query. But this is a different question. – Nodule

Perhaps adding ON CONFLICT IGNORE to CREATE TABLE would be a bit more handy – Milligan

@Nodule What will happen if I do not specify UNIQUE while creating a table, but I have the first column id as a primary key? I checked, it does not update the values if I try to insert anything with the same id with INSERT OR IGNORE. So, do I need a UNIQUE constrain if I have a PRIMARY KEY or is there is anything I'm missing? – Mcdaniels

@Mcdaniels "Ignore" means that nothing happens; this question has nothing to do with updating. – Nodule

It works without "OR IGNORE" to be added for insertion. In fact I'm calling the built-in function SQLiteDatabase.insert(...) directly, so I hope my DB is ok, isn't it? – Colin

@Colin Android's insert() behaves differently; you should replace it with insertOrThrow() or insertWithOnConflict(). – Nodule

"A primary key over both columns would have the same effect", how would this be created? Presumably this is what you have in mind: stackoverflow.com/a/734704 – Fervor

Bear in mind that with INSERT IGNORE you will lose insight if query fails, which may mean you are silently losing data. That's not what database are designed for. Use: INSERT IGNORE only if you're fully aware of the consequences. If possible, use ON CONFLICT DO NOTHING instead (SQLite 3.24+ required), for any older SQLites INSERT INTO ... SELECT ... WHERE NOT EXISTS(SELECT 1 ... FROM WHERE ...) is safer... even though is pretty cumbersome. – Pillowcase

Answer 3

For a unique column, use this:

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

For more information, see: sqlite.org/lang_insert

Burweed

This does not work for me. it always inserts as in insert or replace into my_table (col1, col2) values('1','2'); will add multiple rows of 1 2 – Thoughtless

I might add that it does work well if the constraint is put in place Unique(col1,col2) and you also have col3, because an insert or ignore would fail where this will update col3 to the new value. insert or replace into my_table values('1','2','5') replaces a row '1','2','3' – Thoughtless

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

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

Grier

Answer 5

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

Sublunary