Increment or Insert (UPSERT)

The ON CONFLICT clause is a non-standard extension specific to SQLite.

This way of using ON CONFLICT is also referred to as UPSERT (which is not a keyword in SQLite).

CREATE TABLE counters (
    name TEXT UNIQUE NOT NULL,
    number INTEGER NOT NULL
);

INSERT INTO counters (name, number)
  VALUES ('apple', 1)
  ON CONFLICT(name) DO UPDATE SET number = number + 1;

SELECT * FROM counters;

INSERT INTO counters (name, number)
  VALUES ('apple', 1)
  ON CONFLICT(name) DO UPDATE SET number = number + 1;

SELECT * FROM counters;

$ sqlite < examples/increment-or-insert.sql

apple|1
apple|2