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

score 609 · Answer 1 · answered Oct 13 '13 at 08:10

609

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)

answered Oct 13 '13 at 08:10

CL.

173,858
17
217
259

26

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)`? – Aur Saraf Feb 23 '15 at 16:27
2

Reading the ID of an existing row is possible only with a SELECT query. But this is a different question. – CL. Feb 23 '15 at 18:05
3

Perhaps adding `ON CONFLICT IGNORE` to `CREATE TABLE` would be a bit more handy – defhlt Apr 12 '15 at 13:44
@CL. 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? – rightaway717 Oct 06 '15 at 12:25
1

@rightaway717 "Ignore" means that nothing happens; this question has nothing to do with updating. – CL. Oct 06 '15 at 13:23
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? – Hack06 Aug 04 '17 at 08:05
1

@Hack06 Android's insert() behaves differently; you should replace it with [insertOrThrow()](https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#insertOrThrow(java.lang.String,%20java.lang.String,%20android.content.ContentValues)) or [insertWithOnConflict()](https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#insertWithOnConflict(java.lang.String,%20java.lang.String,%20android.content.ContentValues,%20int)). – CL. Aug 04 '17 at 08:08
"A primary key over both columns would have the same effect", how would this be created? Presumably this is what you have in mind: https://stackoverflow.com/a/734704 – baxx Aug 07 '21 at 13:11
5

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. – Tom Raganowicz Apr 19 '22 at 19:29

Cyclonecode · Accepted Answer · 2018-04-13T06:12:18.783

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.

Ali Bagheri · Answer 3 · 2022-04-11T11:52:52.393

57

For a unique column, use this:

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

For more information, see: sqlite.org/lang_insert

edited Apr 11 '22 at 11:52

answered Dec 20 '17 at 10:10

Ali Bagheri

3,068
27
28

3

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 – Peter Moore Oct 17 '19 at 17:15
2

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'` – Peter Moore Oct 17 '19 at 17:36

score 6 · Answer 4 · edited Jul 30 '17 at 08:10

6

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

edited Jul 30 '17 at 08:10

answered Jun 01 '15 at 11:46

suat dmk

116
1
2

"Insert if not exists" statement in SQLite

4 Answers4

Linked

Related