SQLite UPSERT / UPDATE OR INSERT

Question

I need to perform UPSERT / INSERT OR UPDATE against a SQLite Database.

There is the command INSERT OR REPLACE which in many cases can be useful. But if you want to keep your id's with autoincrement in place because of foreign keys, it does not work since it deletes the row, creates a new one and consequently this new row has a new ID.

This would be the table:

players - (primary key on id, user_name unique)

|  id   | user_name |  age   |
------------------------------
|  1982 |   johnny  |  23    |
|  1983 |   steven  |  29    |
|  1984 |   pepee   |  40    |

Answer 1

Q&A Style

Well, after researching and fighting with the problem for hours, I found out that there are two ways to accomplish this, depending on the structure of your table and if you have foreign keys restrictions activated to maintain integrity. I'd like to share this in a clean format to save some time to the people that may be in my situation.

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
INSERT OR IGNORE INTO players (user_name, age) VALUES ('steven', 32); 

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

And that's all!

EDIT

As Andy has commented, trying to insert first and then update may lead to firing triggers more often than expected. This is not in my opinion a data safety issue, but it is true that firing unnecessary events makes little sense. Therefore, a improved solution would be:

-- Try to update any existing row
UPDATE players SET age=32 WHERE user_name='steven';

-- Make sure it exists
INSERT OR IGNORE INTO players (user_name, age) VALUES ('steven', 32);