CODEWITHSUNDEEP
javasqldatabasesqlite
StefanOverFlow
StefanOverFlow

Reputation: 409

SQLite Update or Insert, not Upsert

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:

  1. Insert: time_abs=5 volume=25
  2. before I do that it should just check if there is already a value at time_abs=5
  3. yes it is --> volume=20 (is already there) --> new volume=20+25 (old plus new volume)
  4. no it is not --> volume=25
  5. 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.

Upvotes: 1

Views: 312

Answers (1)

CL.
CL.

Reputation: 180060

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;

Upvotes: 1

Related Questions