SQLite “INSERT OR REPLACE INTO” not working

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.

  • how to calibrate the orientation sensor in android?
  • Data binding expression not compiling
  • How to stop other apps playing music from my current activity?
  • bind/unbind service example (android)
  • Android Studio with two monitors
  • Change the Android bluetooth device name
  • ActionBar pre Honeycomb
  • How to know which view inside a specific ListView item that was clicked
  • Application too big? Unable to execute dex: Cannot merge new index into a non-jumbo instruction
  • How to get crash dialog with report button
  • How to run or debug on android phone instead of emulator?
  • Android Floating Action Button API 19 (KitKat)
  • 4 Solutions collect form web for “SQLite “INSERT OR REPLACE INTO” not working”

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

    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

    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.

    I usually use this code for that:

    Cursor cur = mDatabase.rawQuery("select * from NICKS where id_nick='"+id_nick+"');
    cur.moveToFirst();
    if(cur.getCount()>0){
         update...
    }else{
         insert...
    }
    
    Android Babe is a Google Android Fan, All about Android Phones, Android Wear, Android Dev and Android Games Apps and so on.