Android SQLite Insert or Update

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 2initialValues.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.

this is your method SQLiteDatabase.insertWithOnConflict(). to understand what it does refer to this document on sqlite

SQLiteDatabase.replace() does this, it basically calls:

insertWithOnConflict(table, nullColumnHack, initialValues, CONFLICT_REPLACE);

Too bad the documentation is not very clear.