SQLite INSERT - ON DUPLICATE KEY UPDATE (UPSERT)

MySQL has something like this:

INSERT INTO visi开发者_开发知识库ts (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)

---

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.

---

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;

---

I'd prefer UPDATE (+ INSERT if UPDATE fails). Less code = fewer bugs.

---

The current answer will only work in sqlite OR mysql (depending on if you use OR or not). So, if you want cross dbms compatibility, the following will do...

REPLACE INTO `visits` (ip, value) VALUES ($ip, 0);

---

You should use memcached for this since it is a single key (the IP address) storing a single value (the number of visits). You can use the atomic increment function to insure there are no "race" conditions.

It's faster than MySQL and saves the load so MySQL can focus on other things.