122

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)
Improve this question
0

5 Answers 5

Reset to default
133 
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Just for the record, REPLACE is not an option.
Regarding the "another great solution" link, I would also consider a different answer to the same question: stackoverflow.com/a/418988/3650835
77

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;
Improve this answer

3 Comments

I was wondering if you can do multiple upsert like this in one transaction, i.e. with the Python executemany() function?
{"SQL logic error or missing database\r\nnear \"ON\": syntax error" when executing above command,got exception
@jithu Which version of SQLite are you using?
19

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

Improve this answer

3 Comments

Thanks! @Sam (stackoverflow.com/questions/418898/…) seems to agree with you. I also prefer this approach.
@Smith I meant using plain UPDATE and INSERT statements and checking return value.
This doesn't have atomicity it is possible the INSERT will fail if some other process inserted in between.
11

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);
Improve this answer

4 Comments

The accepted answer works on SQLite (that was my aim). REPLACE will work on SQLite too, but on MySQL it will always reset the counter to 0 - while the query will be portable, the end result will differ a lot.
You're right, I thought the OP was looking for something that was portable. I realize REPLACE INTO won't work with all cases, esp where PK preservation is needed, but will for many cases.
Failing cleanly instead of discarding data is a feature, not a bug.
Replace always removes the row first, so it is messy.
-4

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.

Improve this answer

4 Comments

If the data isn't that important, yes. However, if this is being used on a busy site where many IPs are hitting the service, the memcached instance(s) might get full and cause some content to be dropped. Backing up memcached contents would also be interesting (if required.)
@ElliotFoster Memcached can handle as much data as the RAM you throw at it (if you want persitance also then use redis or membase). If you are getting over 1 million visitors day then you can probably afford giving your memcache instance more than 30MB of ram (which is the default I think). However, it can certainly handle a much higher load than SQLite and MySQL for the amount of memory you give it - there just isn't any comparison.
Please do not mistake my comment as a vote against memcache, as I think it is a fantastic tool. As is redis (I cannot speak for membase, as I've not used it.) However, memcache/redis are not the most reliable stores. Yes, redis has persistence, but data is persisted to disk on an interval (last I looked) and memcache not at all. Like I said, if the data isn't important (or can be reproduced easily) then memcache and company are great. The original post was also asking about sqlite, which is very different from MySQL and likely means they are limited in other ways.
You can configure Redis to persist data as fast as you want (at X number of seconds or at Y number of changes).

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.