Android SQLite ON CONFLICT UPDATE is possible?

Question

I need to solve this problem.

I started my project from the wrong concept that ON CONFLICT REPLACE will do an update of the row content when it finds a conflict. Also I was thinking that in that insert (with the conflict) if I don't put a value for a column it wont do the replace but keep the old value. Instead I just discovered that the ON CONFLICT REPLACE if it finds a conflict it will do a delete->insert and if it can't find a value for a column (where I put null for not updating the field) it will replace the null value with the default column value in order to make successfully the insert.

So know I have this big root problem :D

As you understand what I want to reach is this:

I always do the insert, if it finds already a row with the same unique keys (conflict) it will only update the values. If for a field it wont find the values ( I don't put it in the ContentValues ) it wont update those field but keep the old one (like a normal update query where you don't put a column for update it).

To solve my problem I tought these solutions:

• ON CONFLICT UPDATE (this will only work when you have only 1 unique fields, if you have 2 or more unique fields that throw the conflict it will crash)
• UPDATE ON FAIL INSERT where it always try to do an update. If it fails because it can't find the row related to the record (it's not yet present in the db) it will try to do an insert

Some one has already thought about this problem and has a solution? Or maybe we can just try to find a solution :)

I think that the best way is to keep things on low level (db level) in order to keep a good level of performance.

Answer 1

Following SQLite3 official Documentation is not possible to automatically do a ON CONFLICT UPDATE but only a REPLACE that is like DELETE -> INSERT (new values).