Alternative to SQLite INSERT ... ON CONFLICT ... WHERE ... DO UPDATE SET

one text

Solution:

ON CONFLICT... or was added to SQLite in version 3.24.0.

In earlier versions you can get similar functionality with 2 separate statements.

First try to update the table:

UPDATE taxa 
SET rank = ?, parent_id = ?
WHERE taxon_id = ?;

If a row with the taxon_id = ? exists it will be updated.
If it does not exist nothing will happen.

Then try to insert the new row with INSERT OR IGNORE:

INSERT OR IGNORE INTO taxa (taxon_id, rank, parent_id) VALUES (?, ?, ?);

If a row with the taxon_id = ? exists nothing will happen (I assume that taxon_id is the PRIMARY KEY of the table or at least defined as UNIQUE).
If it does not exist then the new row will be inserted.

Source