UPSERT (ON CONFLICT)

"Upsert" means insert, or update if it already exists — one statement that does whichever one is correct, instead of your application code having to check first. SQLite supports this with a clause added onto INSERT: ON CONFLICT ... DO UPDATE SET .... SQLite also has an older, simpler, but considerably more destructive alternative, INSERT OR REPLACE. This lesson covers both and is explicit about why they behave very differently.

The Problem: Reviewing the Same Recipe Twice

In RecipeBox, a reviews row links one user to one recipe with a rating, and a UNIQUE(user_id, recipe_id) constraint enforces that a user can only leave one review per recipe:

CREATE TABLE reviews (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  recipe_id INTEGER NOT NULL REFERENCES recipes(id),
  rating INTEGER CHECK(rating BETWEEN 1 AND 5),
  comment TEXT,
  UNIQUE(user_id, recipe_id)
);

If a user changes their mind and re-reviews a recipe they already reviewed, a plain INSERT hits that UNIQUE constraint and errors out. What we actually want is for the new review to replace the rating and comment on the existing row. That's exactly what UPSERT is for.

ON CONFLICT ... DO UPDATE SET — The excluded Alias

Add ON CONFLICT(columns) DO UPDATE SET ... after a normal INSERT. When the insert would violate the named conflict target, SQLite runs the UPDATE instead, on the row that already exists. Inside that UPDATE, the special excluded. alias refers to the values that were about to be inserted — the row that "got excluded" from being inserted because of the conflict.

INSERT INTO reviews (user_id, recipe_id, rating, comment)
VALUES (7, 1, 5, 'Perfect on a weeknight!')
ON CONFLICT(user_id, recipe_id) DO UPDATE SET
  rating = excluded.rating,
  comment = excluded.comment;

The first time this runs for user 7 and recipe 1, there's no conflict, so it behaves like a normal insert. The second time user 7 reviews recipe 1, the UNIQUE(user_id, recipe_id) conflict fires, and instead of erroring, SQLite updates the existing row's rating and comment to the new values from excluded — every other column on that row (like id) is left exactly as it was.

ON CONFLICT ... DO NOTHING

When you want a conflict to simply be silently skipped rather than updating anything, DO NOTHING is the ON CONFLICT equivalent of INSERT OR IGNORE from the INSERT lesson — the difference is you can target it at a specific conflicting column list rather than any uniqueness violation on the table.

INSERT INTO reviews (user_id, recipe_id, rating, comment)
VALUES (7, 1, 4, 'Actually, still a 5 - my mistake ignoring this')
ON CONFLICT(user_id, recipe_id) DO NOTHING;
-- No error, no change - the existing review is left exactly as it was

INSERT OR REPLACE — Simpler but Destructive

INSERT OR REPLACE takes a completely different approach: on a conflict, SQLite first deletes the existing conflicting row entirely, then inserts the new row fresh. It is not an in-place update. Two consequences follow directly from that, and both are easy to get burned by:

• Any column you don't explicitly list in the INSERT gets reset to its DEFAULT (or NULL) — the old value is gone, not preserved.
• Because the row is deleted and re-inserted, it gets a brand new rowid (and a new auto-generated id if you don't specify one) — anything elsewhere that referenced the old row's id by foreign key can be left dangling or orphaned.

-- DANGER: if 'comment' is left out, the existing comment is LOST -
-- REPLACE deletes the whole old row first, it doesn't merge columns
INSERT OR REPLACE INTO reviews (user_id, recipe_id, rating)
VALUES (7, 1, 5);
-- The row's old comment is now NULL, and it has a new id

Side by Side