SQLite INSERT - ON DUPLICATE KEY UPDATE (UPSERT)

Question

MySQL has something like this:

INSERT INTO visits (ip, hits) VALUES ('127.0.0.1', 1) ON DUPLICATE KEY UPDATE hits = hits + 1; 

As far as I know this feature doesn't exist in SQLite, what I want to know is if there is any way to achive the same effect without having to execute two queries. Also, if this is not possible, what do you prefer:

1. SELECT + (INSERT or UPDATE) or
2. UPDATE (+ INSERT if UPDATE fails)

Answer 1

INSERT OR IGNORE INTO visits VALUES ($ip, 0); UPDATE visits SET hits = hits + 1 WHERE ip LIKE $ip; 

This requires the "ip" column to have a UNIQUE (or PRIMARY KEY) constraint.

---

EDIT: Another great solution: https://stackoverflow.com/a/4330694/89771.

Answer 2

Since 3.24.0 SQLite also supports upsert, so now you can simply write the following

INSERT INTO visits (ip, hits) VALUES ('127.0.0.1', 1) ON CONFLICT(ip) DO UPDATE SET hits = hits + 1;