Android SQLite Insert or Update

by

I believe that you are asking how to INSERT new rows or UPDATE your existing rows in one step. While that is possible in a single raw SQL as discussed in this answer, I found that it easier to do this in two steps in Android using SQLiteDatabase.insertWithOnConflict() using CONFLICT_IGNORE for conflictAlgorithm. 

ContentValues initialValues = new ContentValues();
initialValues.put("_id", 1); // the execution is different if _id is 2
initialValues.put("columnA", "valueNEW");

int id = (int) yourdb.insertWithOnConflict("your_table", null, initialValues, SQLiteDatabase.CONFLICT_IGNORE);
if (id == -1) {
    yourdb.update("your_table", initialValues, "_id=?", new String[] {"1"});  // number 1 is the _id here, update to variable for your code
}

This example assumes that the table key is set for column “_id”, that you know the record _id, and that there is already row #1 (_id=1, columnA = “valueA”, columnB = “valueB”). Here is the difference using insertWithOnConflict with CONFLICT_REPLACE and CONFLICT_IGNORE

  • CONFLICT_REPLACE will overwrite existing values in other columns to null (ie. columnB will become NULL and the result will be _id=1, columnA = “valueNEW”, columnB = NULL). You lose existing data as result and I do not use it in my code.
  • CONFLICT_IGNORE will skip the SQL INSERT for your existing row #1 and you will SQL UPDATE this row in the next step preserving the content of all other columns (ie. the result will be _id=1, columnA = “valueNEW”, columnB = “valueB”).

When you attempt to insert new row #2 which does not exist yet, the code will only execute the SQL INSERT in the first statement insertWithOnConflict (ie. the result will be _id=2, columnA = “valueNEW”, columnB = NULL).

Beware of this bug which is causing SQLiteDatabase.CONFLICT_IGNORE to malfunction on API10 (and probably API11). The query is returning 0 instead of -1 when I test on Android 2.2.

If you do not know the record key _id or you have a condition that will not create a conflict, you can reverse the logic to UPDATE or INSERT. This will keep your record key _id during UPDATE or create a new record _id during INSERT.

int u = yourdb.update("yourtable", values, "anotherID=?", new String[]{"x"});
if (u == 0) {
    yourdb.insertWithOnConflict("yourtable", null, values, SQLiteDatabase.CONFLICT_REPLACE);
}

The above example assumes that your just want to UPDATE timestamp value in the record for example. If you call insertWithOnConflict first, INSERT will create new record _id due to the difference in the timestamp condition.

Leave a Comment