SQLite Update or Insert, not Upsert

Question

I am writing a simulation application and wand to add values into a table if they exists or just insert these as new values.

Example table:

CREATE TABLE "time_table" ("time_abs" INTEGER UNIQUE , "volume" DOUBLE);
INSERT INTO "time_table" ("time_abs","volume") VALUES (5,20);

Here is what I want to do:

Insert: time_abs=5 volume=25
before I do that it should just check if there is already a value at time_abs=5
yes it is --> volume=20 (is already there) --> new volume=20+25 (old plus new volume)
no it is not --> volume=25
one query only, as I want to implement it into my Java method within batches and prepared statement...

I have found Eric B's answer (click), but I cannot add something like this to it:

UPDATE time_table SET volume=volume+25

Thank you for the attention and have a nice day.

CL. · Accepted Answer · 2015-07-26 06:53:23Z

1

The first SELECT computes the new value if the record already exists. If the record does not exist, it returns no result.

The second SELECT returns the values to be used if the record does not exist.

Combining the two SELECTs with UNION ALL gives us either one or two records. With the LIMIT 1, only the first one is returned.

This one record can then be plugged into the INSERT statement:

INSERT OR REPLACE INTO time_table(time_abs, volume)
SELECT time_abs, volume + 25 FROM time_table WHERE time_abs = 5
UNION ALL
SELECT 5, 25
LIMIT 1;

answered Oct 22, 2013 at 16:36

CL.

182k18 gold badges241 silver badges283 bronze badges

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

1 Answer 1