SQLite 'INSERT OR REPLACE INTO' incorrect behaviour

Question

I'm using SQLite to store an id, uuid, value and a caught value.

I create the table with this command:

CREATE TABLE IF NOT EXISTS statz_fish_caught 
('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
 'uuid' TEXT NOT NULL,
 'value' INTEGER NOT NULL,
 'caught' TEXT NOT NULL);

I use this command to insert (or replace) rows:

INSERT OR REPLACE INTO statz_fish_caught (uuid,value,caught) 
VALUES ('uuid comes here', a number here, 'a certain string').

Let's say you have this data in the table:

http://pasteboard.co/gofap3y.png

This data would imply that UUID1 caught a total of 9 (1+5+3) fishes. 1 of fish type 1, 5 of fish type 2 and 3 of fish type 3. UUID2 has only caught 10 fishes of fish type 1. Now let's imagine UUID1 catches a new fish type: fish type 4. The database should create a new row with an auto incremented value of id 5, with uuid as 'uuid1', value=1 and caught=fish type 4.

Now let's also imagine UUID catches another fish of fish type 3. The row of uuid1 with fish type 3 (it has id 3) should be updated to represent value 4, like this:

http://pasteboard.co/goBZatx.png

Is this possible, if yes, how?

Answer 1

You are using an INTEGER AUTOINCREMENT PRIMARY KEY, and you don't have any other UNIQUE field in your table: this means that it is impossible to have a conflict between the existing rows and the one you are trying to add, therefore there will be no REPLACE

You should change the definition of your table like this:

CREATE TABLE IF NOT EXISTS statz_fish_caught 
('id' INTEGER PRIMARY KEY NOT NULL,
 'uuid' TEXT NOT NULL,
 'value' INTEGER NOT NULL,
 'caught' TEXT NOT NULL);

Be aware that, by changing this, you have to manually put the id value when you add the rows, since it's not managed by the DBMS anymore.

Edit: Here you can find a working example; notice how the value of the primary key hasn't changed.

Edit #2: As suggested by ishmaelMakitla, putting UNIQUE for the UUID field should do the trick; I've updated my answer accordingly.

CREATE TABLE IF NOT EXISTS statz_fish_caught 
('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
 'uuid' TEXT NOT NULL UNIQUE,
 'value' INTEGER NOT NULL,
 'caught' TEXT NOT NULL);

A warning: as stated in the SQLite documentation,

The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

Plus, as you can see in this example, the id of the updated row has changed, which is something that I strongly recommand to avoid.

Edit #3 It took me a while, but this example should do what you want.

The query you have to use is something like this (6 is the caught fishes you want to add):

INSERT OR REPLACE INTO statz_fish_caught (uuid,value,caught) 
VALUES (
     -- (select `id` from statz_fish_caught where uuid = 'uu1'),
     'uu1', 
     (select `value` from statz_fish_caught where uuid = 'uu1') + 6, 
     (select `caught` from  statz_fish_caught where uuid = 'uu1')
);

Note that if you want to keep the current value of the id field for the updating row, you have to uncomment the first line of the values and add the id field in the list of columns of the statement.

Answer 2

In addition to what Cynical said, you should set UUID to UNIQUE - this way, the row with a matching UUID will be replaced. So the table should be modified as follows:

CREATE TABLE `statz_fish_caught` (
`id`    INTEGER NOT NULL,
`uuid`  TEXT NOT NULL UNIQUE,
`value` INTEGER NOT NULL,
`caught`    TEXT NOT NULL,
PRIMARY KEY(id)
);

I have tested this by doing the following:

INSERT OR REPLACE INTO statz_fish_caught 
(uuid,value,caught) VALUES (hex(randomblob(16)), 1, 'caught-1 ');

Then I executed the following query string (note, I used the UUID generated by hex(randomblob(16) in the previous query). So by running :

INSERT OR REPLACE INTO statz_fish_caught (uuid,value,caught) VALUES ('uuid-of-row-to-update', 2, 'caught-1-updated');