Increment counter or insert row in one statement, in SQLite

Question

In SQLite, given this database schema

CREATE TABLE observations (
    src TEXT,
    dest TEXT,
    verb TEXT,
    occurrences INTEGER
);
CREATE UNIQUE INDEX observations_index
    ON observations (src, dest, verb);

whenever a new observation tuple (:src, :dest, :verb) comes in, I want to either increment the "occurrences" column for the existing row for that tuple, or add a new row with occurrences=1 if there isn't already one. In concrete pseudocode:

if (SELECT COUNT(*) FROM observations
        WHERE src == :src AND dest == :dest AND verb == :verb) == 1:
    UPDATE observations SET occurrences = occurrences + 1
        WHERE src == :src AND dest == :dest AND verb == :verb
else:
    INSERT INTO observations VALUES (:src, :dest, :verb, 1)

I'm wondering if it's possible to do this entire operation in one SQLite statement. That would simplify the application logic (which is required to be fully asynchronous wrt database operations) and also avoid a double index lookup with exactly the same key. INSERT OR REPLACE doesn't appear to be what I want, and alas there is no UPDATE OR INSERT.

Beaner

Wouldn't it make more sense to declare occurrences as an INTEGER? – Decrescent

Answer 1

I got this answer from Igor Tandetnik on sqlite-users:

INSERT OR REPLACE INTO observations
VALUES (:src, :dest, :verb,
  COALESCE(
    (SELECT occurrences FROM observations
       WHERE src=:src AND dest=:dest AND verb=:verb),
    0) + 1);

It's slightly but consistently faster than dan04's approach.

Beaner

Sorry if this is obvious, but don't forget to create a unique index as per the OP or this will always create a new counter and never increment: CREATE UNIQUE INDEX observations_index ON observations (src, dest, verb);. – Pellucid

Also note, that it increments the ROWID on each update (because each update is in essence a DELETE + INSERT) – Toughminded

Answer 2

Don't know of a way to do it in one statement, but you could try

BEGIN;
    INSERT OR IGNORE INTO observations VALUES (:src, :dest, :verb, 0);
    UPDATE observeraions SET occurrences = occurrences + 1 WHERE
        src = :src AND dest = :dest AND verb = :verb;
COMMIT;

Decrescent