CODEWITHSUNDEEP
sqlite
BIO
BIO

Reputation: 23

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

I'm running an application that uses SQLite3 version 3.7.17 on Linux. It's erroring out on this statement:

INSERT INTO taxa (taxon_id, rank, parent_id) VALUES (?,?,?)
        ON CONFLICT (taxon_id) WHERE parent_id is NULL
        DO UPDATE SET parent_id=excluded.parent_id,rank=excluded.rank

But the same code runs on version 3.28.0. Is there another way of writing this statement so it can run on 3.7.17?

Upvotes: 2

Views: 1525

Answers (1)

forpas
forpas

Reputation: 164089

ON CONFLICT... or UPSERT 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.

Upvotes: 4

Related Questions