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:

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

score 128 · Answer 1 · edited Jan 21 '20 at 09:07

128

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.

edited Jan 21 '20 at 09:07

Kerem

11,377
5
59
58

answered Apr 27 '10 at 02:30

dan04

87,747
23
163
198

3

Just for the record, `REPLACE` is not an option. – Alix Axel Nov 17 '12 at 03:43
1

Regarding the "another great solution" link, I would also consider a different answer to the same question: http://stackoverflow.com/a/418988/3650835 – KayakinKoder Jun 09 '16 at 17:55

score 62 · Accepted Answer · answered Nov 01 '18 at 10:23

62

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;

answered Nov 01 '18 at 10:23

szmate1618

1,545
1
17
21

3

I was wondering if you can do multiple `upsert` like this in one transaction, i.e. with the Python `executemany()` function? – Radio Controlled Jun 04 '19 at 10:07
{"SQL logic error or missing database\r\nnear \"ON\": syntax error" when executing above command,got exception – jithu Mar 19 '21 at 08:29
2

@jithu Which version of SQLite are you using? – szmate1618 Mar 19 '21 at 09:03

score 19 · Answer 3 · answered Apr 26 '10 at 23:21

19

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

answered Apr 26 '10 at 23:21

codeholic

5,680
3
23
43

1

Thanks! @Sam (http://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace/418988#418988) seems to agree with you. I also prefer this approach. – Alix Axel Apr 27 '10 at 00:08
@Smith I meant using plain UPDATE and INSERT statements and checking return value. – codeholic Nov 17 '13 at 14:47
This doesn't have atomicity it is possible the INSERT will fail if some other process inserted in between. – Robin Lavallée Aug 12 '20 at 15:31

Jacob Thomason · Answer 4 · 2013-05-17T05:59:48.403

9

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

edited May 17 '13 at 05:59

answered Nov 17 '12 at 02:53

Jacob Thomason

3,062
2
17
21

4

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. – Alix Axel Nov 17 '12 at 03:45
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. – Jacob Thomason Dec 07 '12 at 05:22
Failing cleanly instead of discarding data is a feature, not a bug. – Tobu Apr 03 '13 at 06:50
Replace always removes the row first, so it is messy. – Daniel May 14 '21 at 12:14

score -4 · Answer 5 · answered Oct 05 '11 at 15:28

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

answered Oct 05 '11 at 15:28

Xeoncross

55,620
80
262
364

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.) – Elliot Foster Oct 13 '11 at 23:46
@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. – Xeoncross Oct 14 '11 at 15:13
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. – Elliot Foster Oct 15 '11 at 04:26
You can configure Redis to persist data as fast as you want (at X number of seconds or at Y number of changes). – Buffalo Dec 23 '14 at 10:08

SQLite INSERT - ON DUPLICATE KEY UPDATE (UPSERT)

5 Answers5

Linked

Related