I am using a pretty simple INSERT OR REPLACE query. I noticed this bit
in the description of the ON CONFLICT syntax for REPLACE:
> When a UNIQUE constraint violation occurs, the pre-existing rows that
are causing the constraint violation are removed prior to inserting or
updating the current row.
This makes sense in many contexts I image, however it would be
immeasurably useful if an alternate behavior was available, perhaps as a
separate conflict algorithm. That alternate behavior would be to instead
of dropping the row existing, simply update the provided fields in
place, leaving the unmodified fields as is. I'd call this behavior OR
UPDATE as it would be exactly equivalent to doing an UPDATE on the
existing row(s).
With the current behavior I'd have to do a query to see if the row
exists, if not construct SQL for an insert, but if it does construct SQL
for an update. This is complicated by the fact that if a certain field
is a certain value, the other fields should not be modified: they are
left as is in the database.
Perhaps someone with more experience in SQL queries (I have just done
mostly pretty straight forward MySQL and SQLite) can show me how I might
be able to do the same thing using only SQL? I'd rather not write it all
in my client app (written Delphi) just for the brevity that SQL usually
offers.
Thanks for any thoughts and maybe a new option eventually,
-Isaac
