CODEWITHSUNDEEP
sqliteupsert
Sean Mars
Sean Mars

Reputation: 21

How to avoid consume key-id when using upsert with autoincrement key in SQLite?

I have a table like below:

The id is PK and autoincrement.

+-----+--------+--------+
| id  |  kind  |  count |
+-----+--------+--------+

When i using the upsert query bleow, and 100 request that api at same time.

INSERT INTO Views (kind, count)
VALUES(1, 1)
ON CONFLICT(kind)
DO UPDATE SET count = count + 1

I'll get:

+-----+--------+--------+
| id  |  kind  |  count |
+-----+--------+--------+
| 100 | kind-1 |   100  |
+-----+--------+--------+

Upvotes: 1

Views: 341

Answers (2)

fxdeltombe
fxdeltombe

Reputation: 61

I guess your table was built with

create table Views (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  kind VARCHAR UNIQUE,
  count INTEGER
)

and by running 100 times the query INSERT INTO Views (kind, count) VALUES(1, 1) ON CONFLICT(kind) DO UPDATE SET count = count + 1, you will not get what you wrote, but

+-----+-------+--------+
|  id |  kind |  count |
+-----+-------+--------+
|   1 |     1 |   100  |
+-----+-------+--------+

The behavior that may be unwanted is that, when inserting another value (e.g. 2) with the same insert query, you will get

+-----+-------+--------+
|  id |  kind |  count |
+-----+-------+--------+
|   1 |     1 |   100  |
| 101 |     2 |     1  |
+-----+-------+--------+

Just by removing the AUTOINCREMENT keyword in the create table query, you would get what I suppose you want, i.e.

+-----+-------+--------+
|  id |  kind |  count |
+-----+-------+--------+
|   1 |     1 |   100  |
|   2 |     2 |     1  |
+-----+-------+--------+

and, as @DinoCoderSaurus says, AUTOINCREMENT is needed only for tricky cases, "to prevent the reuse of ROWIDs from previously deleted rows" (SQLite doc).

Upvotes: 1

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11181

it is possible to manipulate sqlite_sequence table like this:

UPDATE sqlite_sequence SET seq=(SELECT MAX(id) FROM Views) WHERE name="Views"

which will "reset" sequence to last used id.

This isn't, however, usefull at all. Updating sqlite_sequence during bulk UPSERT would for sure break performance and doing such after operation wouldn't avoid "holes" in key sequence.

Another option is to rewrite all id, but do you really need such?

Upvotes: 1

Related Questions