SQLite "INSERT OR REPLACE INTO" not working

Question

I have to write a query in sqlite to update the record if it exists or insert it if the record do not already exists. I have looked the syntax of INSERT OR REPLACE INTO from here. But in my case, when I execute the query below many times the record is duplicated. i.e If I execute the query 5 times the record is inserted 5 times.

INSERT OR REPLACE INTO NICKS 
    (id_nick,name_nick,date_creation)
 VALUES 
    ('nabeelarif', 'Muhammad Nabeel','2012-03-04')

Have you any idea what I am doing wrong. I am working on android platform and using 'Firefox Sqlite Manager' to test my query.

score 27 · Accepted Answer · answered Mar 05 '12 at 20:36

27

You need to have a unique index on one or a combination of those columns.

CREATE UNIQUE INDEX idx_something ON NICKS (id_nick, name_nick, date_creation);

answered Mar 05 '12 at 20:36

Brad

5,428
1
33
56

Thank you. I created Unique Index on 'id_nick' and it worked. – Muhammad Nabeel Arif Mar 05 '12 at 20:48
I also created one but dont seem to work, can you provide more insight? – Skynet Dec 09 '13 at 06:26

score 6 · Answer 2 · answered Mar 05 '12 at 20:38

In Android, you should use SQLiteDatabase.replace(), which does insert or update. With the Android SQLite implementation, you normally don't use raw queries represented as strings. See http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#replace%28java.lang.String,%20java.lang.String,%20android.content.ContentValues%29

score 4 · Answer 3 · answered Mar 05 '12 at 20:36

4

Do you have a primary key or unique index defined for your table? I believe the attributes that make up the primary key or a unique index are used to determine if a row already exists or not.

answered Mar 05 '12 at 20:36

Jan-Henk

4,864
1
24
38

SQLite "INSERT OR REPLACE INTO" not working

3 Answers3

Linked