Android SQLite Insert or Update

Solution 1:

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.

Solution 2:

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

Solution 3:

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

insertWithOnConflict(table, nullColumnHack, initialValues, CONFLICT_REPLACE);

Too bad the documentation is not very clear.

Solution 4:

The operation name for that is "upsert" and how I solve it is identifying the columns of your table that make a row UNIQUE.

Example: _id, name, job, hours_worked

The columns which we'll use are name and job.

private int getID(String name, String job){
    Cursor c = dbr.query(TABLE_NAME,new String[]{"_id"} "name =? AND job=?",new String[]{name,job},null,null,null,null);
    if (c.moveToFirst()) //if the row exist then return the id
        return c.getInt(c.getColumnIndex("_id"));
    return -1;
}

In your database manager class:

public void upsert(String name, String job){
    ContentValues values = new ContentValues();
    values.put("NAME",name);
    values.put("JOB",job);

    int id = getID(name,job);
    if(id==-1)
        db.insert(TABLE_NAME, null, values);
    else
        db.update(TABLE_NAME, values, "_id=?", new String[]{Integer.toString(id)});
}

Solution 5:

SQLiteDatabase.replace() is probably what you are looking for. I haven't tried it but the doc says it returns the row ID of the newly inserted row, so it may work.