SQLite INSERT – ON DUPLICATE KEY UPDATE (UPSERT)

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:

SELECT + (INSERT or UPDATE) or
UPDATE (+ INSERT if UPDATE fails)

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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.

Method 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;

Method 3

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

Method 4

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);

Method 5

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating