INSERT or UPDATE (replace)

ON CONFLICT

This statement will try to INSERT a row, but if the name is already in the database then it will update the recent field. The exclude.recent is the value we tried to insert. It can be used instead of duplicating the value of the recent field.

The first statement will INSERT a row. (because ‘Joe’ was not in the database yet)

The second statement will UPDATE the recent field of Joe.

CREATE TABLE score (
    name TEXT UNIQUE NOT NULL,
    recent INTEGER NOT NULL
);

INSERT INTO score (name, recent) VALUES('Joe', 23)
  ON CONFLICT(name) DO UPDATE SET recent=excluded.recent;

SELECT * FROM score;

INSERT INTO score (name, recent) VALUES('Joe', 42)
  ON CONFLICT(name) DO UPDATE SET recent=excluded.recent;

SELECT * FROM score;

CREATE TABLE score (
    name TEXT UNIQUE NOT NULL,
    recent INTEGER NOT NULL
);

INSERT INTO score (name, recent) VALUES('Joe', 23)
  ON CONFLICT(name) DO UPDATE SET recent=excluded.recent;

SELECT * FROM score;

INSERT INTO score (name, recent) VALUES('Joe', 42)
  ON CONFLICT(name) DO UPDATE SET recent=excluded.recent;

SELECT * FROM score;

Joe|23
Joe|42