Perform UPSERT / INSERT OR UPDATE against a SQLite Database

Option 1: You can afford deleting the row

In other words, you don't have foreign key, or if you have them, your SQLite engine is configured so that there no are integrity exceptions. The way to go is INSERT OR REPLACE. If you are trying to insert/update a player whose ID already exists, the SQLite engine will delete that row and insert the data you are providing. Now the question comes: what to do to keep the old ID associated?

Let's say we want to UPSERT with the data user_name="steven" and age=32.

Look at this code:

INSERT INTO players (id, name, age)
VALUES(coalesce((select id from players where user_name="steven"),(select max(id)from drawings)+1),32)

The trick is in coalesce. It returns the id of the user "steven" if any, and otherwise, it returns a new fresh id.

 

Option 2: You cannot afford deleting the row

After monkeying around with the previous solution, I realized that in my case that could end up destroying data, since this ID works as a foreign key for other table. Besides, I created the table with the clause ON DELETE CASCADE, which would mean that it'd delete data silently. Dangerous.

So, I first thought of a IF clause, but SQLite only has CASE. And this CASE can't be used (or at least I did not manage it) to perform one UPDATE query if EXISTS(select id from players where user_name="steven"), and INSERT if it didn't. No go.

And then, finally I used the brute force, with success. The logic is, for each UPSERT that you want to perform, first execute a INSERT OR IGNORE to make sure there is a row with our user, and then execute an UPDATE query with exactly the same data you tried to insert.

Same data as before: user_name="steven" and age=32.

-- make sure it exists --
INSERTOR IGNORE INTO players (user_name, age)
VALUES("steven",32);

-- make sure it has the right data --
UPDATE players 
SET user_name="steven", age=32WHERE user_name="steven";