Upserts and conflict handling

An upsert is one write that means “create this identity, or merge into the row that already owns it.” The unique constraint is the gate. Name that identity, decide whether a conflict is a no-op or a merge, then read the stored row so the result matches the rule you intended—not the race you hoped to win in application code.

A duplicate insert is a uniqueness conflict, not a retry

Last lesson, a second INSERT with the same primary key failed. That failure is useful. Two accounts must not share an email. The database is not being difficult; it is protecting the one-row meaning of the table.

Run the next script as written. The second insert should abort. The following SELECT never runs, because this runner stops on the first error. That is the same shape as a production statement that has no conflict clause: the whole command fails, and you must handle the error in the client.

CREATE TABLE accounts (
  account_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL
);

INSERT INTO accounts (email, display_name)
VALUES ('ada@example.com', 'Ada');

INSERT INTO accounts (email, display_name)
VALUES ('ada@example.com', 'Ada Lovelace');

SELECT account_id, email, display_name
FROM accounts;

Edit the query, predict the rows it will return, then run it.

SELECT then INSERT is not a uniqueness check

A common application pattern is: look up the email, and insert only if the lookup is empty. In one tab, on toy data, it looks perfect. Under load, two requests can both see “missing,” both insert, and one of them still hits UNIQUE—or, if you skipped the constraint, you store two Adas.

The lookup below is not a lock. It is a snapshot of this isolated database at that moment. The unique constraint on email is the only check that still works when two writers arrive together. Put the uniqueness in the schema. Put the “what to do on conflict” in the insert. The next lesson covers transactions; they do not replace this rule.

CREATE TABLE accounts (
  account_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL
);

SELECT email
FROM accounts
WHERE email = 'ada@example.com';

INSERT INTO accounts (email, display_name)
VALUES ('ada@example.com', 'Ada');

SELECT account_id, email, display_name
FROM accounts;

Edit the query, predict the rows it will return, then run it.

ON CONFLICT DO NOTHING is insert-if-absent

When a duplicate should be ignored—idempotent imports, “ensure this row exists,” webhook retries—use DO NOTHING. The first row for Ada stays. Grace is new, so she is inserted. No error. No second Ada.

Name the conflict target: ON CONFLICT(email) matches the unique column. If you omit the target, SQLite treats a conflict on any unique constraint as a no-op. Prefer the named form so a later unique index cannot silently swallow a different kind of duplicate.

CREATE TABLE accounts (
  account_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL
);

INSERT INTO accounts (email, display_name)
VALUES ('ada@example.com', 'Ada');

INSERT INTO accounts (email, display_name)
VALUES ('ada@example.com', 'Ada Lovelace')
ON CONFLICT(email) DO NOTHING;

INSERT INTO accounts (email, display_name)
VALUES ('grace@example.com', 'Grace')
ON CONFLICT(email) DO NOTHING;

SELECT account_id, email, display_name
FROM accounts
ORDER BY account_id;

Edit the query, predict the rows it will return, then run it.

ON CONFLICT DO UPDATE is a merge

excluded is the row the insert would have created. DO UPDATE SET title = excluded.title copies those incoming values onto the existing row. Columns you do not mention keep their stored values. That is the difference between a merge and a rewrite.

The first statement creates NB-1. The second statement is a two-row upsert: revise the notebook price and title, and insert the sticker. One statement, two identities, two outcomes. Read every remaining row. The notebook should keep product_id 1. A merge is not a new product.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  sku TEXT NOT NULL UNIQUE,
  title TEXT NOT NULL,
  price_cents INTEGER NOT NULL CHECK (price_cents >= 0)
);

INSERT INTO products (sku, title, price_cents)
VALUES ('NB-1', 'SQL notebook', 1499);

INSERT INTO products (sku, title, price_cents)
VALUES
  ('NB-1', 'SQL notebook (revised)', 1299),
  ('ST-1', 'Database sticker', 299)
ON CONFLICT(sku) DO UPDATE SET
  title = excluded.title,
  price_cents = excluded.price_cents;

SELECT product_id, sku, title, price_cents
FROM products
ORDER BY product_id;

Edit the query, predict the rows it will return, then run it.

excluded is the incoming payload, not the stored counter

If the insert always sends login_count = 1, then SET login_count = excluded.login_count would freeze the counter at 1. The stored row owns the counter. Increment accounts.login_count. Use excluded for values that should come from this request, such as a new display name.

Write the merge as a sentence before you write SQL: “On a known email, take the latest name and add one login.” If you cannot say that sentence, you are not ready to choose SET expressions.

CREATE TABLE accounts (
  account_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,
  login_count INTEGER NOT NULL DEFAULT 0
);

INSERT INTO accounts (email, display_name, login_count)
VALUES ('ada@example.com', 'Ada', 1);

INSERT INTO accounts (email, display_name, login_count)
VALUES ('ada@example.com', 'Ada Lovelace', 1)
ON CONFLICT(email) DO UPDATE SET
  display_name = excluded.display_name,
  login_count = accounts.login_count + 1;

SELECT account_id, email, display_name, login_count
FROM accounts;

Edit the query, predict the rows it will return, then run it.

CREATE TABLE accounts (
  account_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,
  login_count INTEGER NOT NULL DEFAULT 0
);

INSERT INTO accounts (email, display_name, login_count)
VALUES ('ada@example.com', 'Ada', 12);

INSERT OR REPLACE INTO accounts (account_id, email, display_name)
VALUES (1, 'ada@example.com', 'Ada Lovelace');

SELECT account_id, email, display_name, login_count
FROM accounts;

Edit the query, predict the rows it will return, then run it.

A WHERE on DO UPDATE decides whether the new values win

Conflict found does not always mean overwrite. High scores should rise, never fall. Inventory imports should not apply a negative shipment. SQLite allows DO UPDATE SET ... WHERE .... If the WHERE is false, the existing row stays. That is still a successful statement: the conflict was handled, and the merge rule said “keep.”

CREATE TABLE high_scores (
  player TEXT NOT NULL PRIMARY KEY,
  score INTEGER NOT NULL CHECK (score >= 0)
);

INSERT INTO high_scores (player, score)
VALUES ('ada', 1200);

INSERT INTO high_scores (player, score)
VALUES ('ada', 900)
ON CONFLICT(player) DO UPDATE SET
  score = excluded.score
WHERE excluded.score > high_scores.score;

INSERT INTO high_scores (player, score)
VALUES ('ada', 1500)
ON CONFLICT(player) DO UPDATE SET
  score = excluded.score
WHERE excluded.score > high_scores.score;

SELECT player, score
FROM high_scores;

Edit the query, predict the rows it will return, then run it.

The conflict target is the real identity, not a convenient column

Two suppliers can sell a product labeled NB-1. Uniqueness lives on (supplier_id, sku), not on sku alone. Name that pair in ON CONFLICT. Adding stock should add to the matching supplier's row, not to a different vendor's notebook.

If you conflict on the wrong columns, you will merge the wrong rows—or never merge, and then hit a different unique constraint with a less helpful error. Draw the identity first. The SQL only records that drawing.

CREATE TABLE catalog_items (
  item_id INTEGER PRIMARY KEY,
  supplier_id INTEGER NOT NULL,
  sku TEXT NOT NULL,
  title TEXT NOT NULL,
  stock INTEGER NOT NULL CHECK (stock >= 0),
  UNIQUE (supplier_id, sku)
);

INSERT INTO catalog_items (supplier_id, sku, title, stock)
VALUES
  (10, 'NB-1', 'Notebook from North', 4),
  (20, 'NB-1', 'Notebook from West', 2);

INSERT INTO catalog_items (supplier_id, sku, title, stock)
VALUES
  (10, 'NB-1', 'Notebook from North', 3),
  (10, 'ST-1', 'Sticker pack', 8)
ON CONFLICT(supplier_id, sku) DO UPDATE SET
  title = excluded.title,
  stock = catalog_items.stock + excluded.stock;

SELECT item_id, supplier_id, sku, title, stock
FROM catalog_items
ORDER BY supplier_id, sku;

Edit the query, predict the rows it will return, then run it.

An upsert checklist

1. Write the one-row meaning of the table, including which columns identify a row.
2. Put that identity in a PRIMARY KEY or UNIQUE constraint. No constraint, no conflict.
3. Decide the conflict policy: ignore, merge specified columns, or fail (plain INSERT).
4. For a merge, write a sentence: which incoming columns win, which stored columns stay, which counters increment.
5. Name the conflict target. Prefer the columns that match the identity, not “any unique index.”
6. Avoid INSERT OR REPLACE unless deleting the old row is the product decision.
7. Run the upsert twice. The second run should be a stable merge or a no-op, not a second identity.
8. SELECT the identity and nearby rows. Confirm preserved columns did not reset.

Independent lab: merge lesson progress without losing a best score

A learner can retry a quiz. The stored row should keep the best score, count every attempt, and become complete once any attempt is complete. A later worse score must not erase 10. A new lesson slug should insert. The starter already encodes that merge. Read it until you can predict all three rows, then run it. Afterward, change the second batch so the inserts lesson scores 3 instead of 9, and confirm best_score stays 9 while attempt_count still grows.

CREATE TABLE lesson_progress (
  learner_id INTEGER NOT NULL,
  lesson_slug TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('started', 'complete')),
  best_score INTEGER NOT NULL CHECK (best_score BETWEEN 0 AND 10),
  attempt_count INTEGER NOT NULL CHECK (attempt_count >= 1),
  PRIMARY KEY (learner_id, lesson_slug)
);

INSERT INTO lesson_progress (learner_id, lesson_slug, status, best_score, attempt_count)
VALUES
  (1, 'insert-update-and-delete', 'started', 4, 1),
  (1, 'views-and-materialized-views', 'complete', 10, 2);

INSERT INTO lesson_progress (learner_id, lesson_slug, status, best_score, attempt_count)
VALUES
  (1, 'insert-update-and-delete', 'complete', 9, 1),
  (1, 'views-and-materialized-views', 'complete', 8, 1),
  (1, 'upserts-and-conflict-handling', 'started', 0, 1)
ON CONFLICT(learner_id, lesson_slug) DO UPDATE SET
  status = CASE
    WHEN excluded.status = 'complete' OR lesson_progress.status = 'complete'
      THEN 'complete'
    ELSE excluded.status
  END,
  best_score = MAX(lesson_progress.best_score, excluded.best_score),
  attempt_count = lesson_progress.attempt_count + 1;

SELECT learner_id, lesson_slug, status, best_score, attempt_count
FROM lesson_progress
ORDER BY lesson_slug;

Edit the query, predict the rows it will return, then run it.

Common mistakes to avoid

• Checking existence in the application and treating that as a lock.
• Omitting UNIQUE / primary key, then wondering why “upsert” inserted twins.
• Conflicting on a convenient column that is not the real identity.
• Setting counters to excluded values that were only defaults on the insert.
• Using INSERT OR REPLACE and losing omitted columns or child rows.
• Overwriting a high score, a paid flag, or an audit timestamp because the SET list was copied from a full row dump.
• Leaving the conflict unnamed so a new unique index changes which duplicates are swallowed.
• Skipping the verification SELECT after a “successful” upsert.

Lesson review

• I can explain why a unique constraint is the real uniqueness check.
• I can use ON CONFLICT DO NOTHING for idempotent inserts.
• I can merge with DO UPDATE and excluded without rewriting the whole row.
• I can increment stored counters instead of replacing them from the payload.
• I can explain why INSERT OR REPLACE is a delete-then-insert.
• I can name a composite conflict target and gate a merge with WHERE.