CODEWITHSUNDEEP
androidsqlite
Chris
Chris

Reputation: 4368

Simulate ON DUPLICATE KEY UPDATE in SQLITE Android

I am trying to find a way to get INSERT...ON DUPLICATE KEY UPDATE from MySQL working in SQLite. The problem is that my current attempts are always failing due to the fact, that SQLite always deletes the row first and inserts it with the new values and the same primary key. When I now have a foreign key constraint ON DELETE CASCADE on this primary key in another table, the entries in the other tables always get deleted.

What I've tried:

db.insertWithOnConflict(tableName, null, values, SQLiteDatabase.CONFLICT_REPLACE);
db.replace(tableName, null, values);

Both methods first remove the entry and re-insert it.

Is there any method to prevent this kind of behaviour and just update the entry's values, except for the primary?

My implementation based on the answer:

public long insertUpdate(int id, ContentValues values) {
    if (!isOpen()) {
        open();
    }


    ContentValues valuesToInsert = new ContentValues();
    valuesToInsert.putAll(values);
    valuesToInsert.put(indexKey, id);
    long result = db.insertWithOnConflict(tableName, null, valuesToInsert, SQLiteDatabase.CONFLICT_IGNORE);
    if (result == id) {
        update(id, values);
    }

    return id;
}

Upvotes: 4

Views: 1633

Answers (1)

PPartisan
PPartisan

Reputation: 8231

I've never tried it before, but could you first attempt to call insertWithOnConflict() with a CONFLICT_FAIL parameter and then, if it returns with a failed id code, then run an update() for the same position?

int result = insertWithOnConflict(YourDbHelperClass.tableName, null, values, SQLiteDatabase.CONFLICT_FAIL);
if (result == -1) update(tableName, values, YourDbHelperClass.rowId + "=?", new String[] { "x" }); //x being row number

Just a thought.

Upvotes: 2

Related Questions