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 always fail due to the fact that SQLite always deletes the first row and inserts it with 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 records in the other tables are always deleted.
What I have tried:
db.insertWithOnConflict(tableName, null, values, SQLiteDatabase.CONFLICT_REPLACE);
db.replace(tableName, null, values);
Both methods first delete the entry and re-insert it.
Is there any method to prevent this behavior and just update the input values other than the primary?
My implementation is 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;
}
source to share
I've never tried this before, but could you first try calling insertWithOnConflict()
with a parameter CONFLICT_FAIL
and then if it returns with an unsuccessful id code then run 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.
source to share