Perhaps the SQLite ON CONFLICT UPDATE Android app?

Question

I need to solve this problem.

I started my project with the wrong concept that ON CONFLICT REPLACE will update the contents of a string when a conflict is detected. I also thought that in this insert (with a conflict), if I do not supply a value for the column, it will not replace, but retain the old value. Instead, I just found that ON CONFLICT REPLACE, if it detects a conflict, will insert delete->, and if it cannot find the value for the column (where I put null to not update the field), it will replace the null value with default column value to insert successfully.

You know, I have this big root problem: D

As you know, what I want to achieve is:

I always insert if it already finds a string with the same unique keys (conflict), it will only update the values. If for a field it does not find a value (I do not put them in ContentValues), it will not update this field, but retain the old one (for example, a regular update request, where you will not put a column to update it).

To solve my problem, I solved these solutions:

• ON CONFLICT UPDATE (this will only work if you have only 1 unique field, if you have 2 or more unique fields that cause a conflict it will work)
• UPDATE ON FAIL INSERT where it always tries to update. If it fails because it cannot find the line associated with the record (it is not already present in db), it will try to insert

Has someone already thought about this problem and has a solution? Or maybe we’ll just try to find a solution :)

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

Answer 1

According to the official SQLite3 documentation, it is not possible to automatically perform a CONFLICT UPDATE, but only REPLACE, which is similar to DELETE → INSERT (new values).