How to do INSERT with an UPDATE on duplicate using SQLite

How to do an UPDATE from an INSERT with SQLite when there is a duplicate key value.

With SQLite you cannot do the simple MySQL INSERT on duplicate key UPDATE:

INSERT INTO `table` (id, name, price, quantity) VALUES(1, 'test', '2.50', 164)
    ON DUPLICATE KEY UPDATE `quantity` = 164, `price` = '2.50'

Instead, you have to do what is called an upsert.

The concept is very similar to the MySQL example above. The differences being you have to specify which column is the indexed/key column (unique) and then state the DO UPDATE:

INSERT INTO users(username,score) VALUES('Johnny', 388) 
ON CONFLICT(username) DO UPDATE SET score = '388';

Had you need to update multiple columns simply separate each update instance with a comma:

DO UPDATE SET score = '388', rating = 'A';

From the example if a row already has the value “Johnny” in the name column then the score column value will be updated to be 388.