UPSERT (ON CONFLICT) and Bulk INSERT Applications

The data we'll use — stock and stock_in

UPSERT (a blend of UPDATE and INSERT —

the operation "update if it exists, insert if it doesn't" in a single statement) is implemented via the INSERT … ON CONFLICT(key) DO UPDATE syntax.

If the row collides with a primary key or UNIQUE constraint, the update runs; if not, it's inserted as-is.

Before the exercises, check the column definitions and sample data for the two tables — stock and stock_in.

① Run PRAGMA table_info(stock); to see column names, types, and the primary key of stock (UPSERT assumes sku is the primary key).

② Preview the two tables with SELECT * FROM stock ORDER BY sku; and SELECT * FROM stock_in;.

ON CONFLICT DO UPDATE — update if it exists, insert if it doesn't

Writing INSERT INTO table(...) VALUES (...) ON CONFLICT(key_col) DO UPDATE SET col = ... means: if the row you're trying to insert collides with the key declared in ON CONFLICT (the primary key or a UNIQUE column), the DO UPDATE update runs; if there's no collision, the row is simply inserted.

Inside DO UPDATE, a special table name excluded lets you reference "the values of the row you tried to insert."

Write qty = qty + excluded.qty and, on collision, the existing qty gets incremented by the qty you tried to insert.

Use qty = excluded.qty for overwrite and qty = qty + excluded.qty for accumulation, depending on what you need.

-- Collides with existing sku → DO UPDATE adds to qty
INSERT INTO stock(sku, name, qty, price)
VALUES ('A001', 'Pen', 5, 80)
ON CONFLICT(sku) DO UPDATE SET qty = qty + excluded.qty;

-- New sku → no collision, just inserted
INSERT INTO stock(sku, name, qty, price)
VALUES ('A006', 'Marker', 3, 120)
ON CONFLICT(sku) DO UPDATE SET qty = qty + excluded.qty;

SELECT sku, name, qty FROM stock WHERE sku IN ('A001', 'A006');

Imagine the requirement: "in arrival processing, add to stock for existing skus, and register new ones for unknown skus." (Run it correctly and the explanation appears.)

① Before the UPSERT, run SELECT sku, name, qty FROM stock WHERE sku IN ('A001','A006') ORDER BY sku; to confirm A001 exists and A006 doesn't.

② For the existing A001, UPSERT with name Pen, qty 15, price 80. On collision, add the inserted value to the existing qty (not overwrite).

③ For the unknown A006, run the same kind of UPSERT with name Stapler, qty 3, price 200 (no collision, so it's inserted).

④ Finally, re-run the same SELECT and confirm that A001 was added to and A006 was inserted.

DO NOTHING — do nothing on collision

When you want neither to update nor to insert — that is, "silently skip on collision" — use ON CONFLICT(key) DO NOTHING.

With no collision the row is inserted; on collision the row is ignored without an error.

In the example below, trying to insert the existing A002 with DO NOTHING collides and is ignored, while the new A007 is inserted.

-- Ignore on collision (DO NOTHING)
INSERT INTO stock(sku, name, qty, price)
VALUES ('A002', 'Note', 999, 999)
ON CONFLICT(sku) DO NOTHING;

-- A002 keeps its original values (qty 60 / price 250)
SELECT sku, name, qty, price FROM stock WHERE sku = 'A002';

Imagine the requirement: "when ingesting multiple rows into the stock master, never overwrite existing skus and only add the unknown ones."

① Before the UPSERT, run SELECT sku, name, qty, price FROM stock WHERE sku IN ('A003','A007') ORDER BY sku; to confirm A003 exists and A007 doesn't.

② Try to insert the existing A003 with name Clip, qty 0, price 0, using DO NOTHING so it's ignored on collision.

③ Add the unknown A007 with name Eraser, qty 50, price 90 using the same DO NOTHING UPSERT.

④ Finally, re-run the same SELECT and confirm A003 is unchanged and A007 was added.

Multi-row UPSERT — combine bulk INSERT with ON CONFLICT

When you attach ON CONFLICT to a multi-row INSERT — VALUES (...),(...),(...) separated by commas — each row independently "updates on collision, inserts if not."

You can reflect arrival data in one statement, replacing a procedural loop that branches between UPDATE and INSERT per row with a single SQL.

Even in a multi-row UPSERT, excluded still means "the value you tried to insert for this row," so writing qty = qty + excluded.qty applies "add for existing rows, insert as-is for new rows" on a per-row basis.

This article's final exercise is a bulk UPSERT of all 3 rows of stock_in (A001 / A004 / A006).

-- Multi-row UPSERT: existing rows accumulate qty and refresh price; new rows are inserted
INSERT INTO stock(sku, name, qty, price)
VALUES
  ('A002', 'Note', 10, 260),
  ('A005', 'Glue', 20, 190),
  ('A007', 'Ruler', 15, 90)
ON CONFLICT(sku) DO UPDATE
  SET qty = qty + excluded.qty,
      price = excluded.price;

SELECT sku, name, qty, price FROM stock ORDER BY sku;

Imagine the requirement: "reflect all 3 rows of the arrival table stock_in into stock in a single statement." This is the article's final exercise.

① Before the UPSERT, run SELECT sku, name, qty FROM stock WHERE sku IN ('A001','A004','A006') ORDER BY sku; to confirm A001 / A004 exist and A006 doesn't.

② Against stock, insert 3 rows in one INSERT — A001 (name Pen / qty 50 / price 80), A004 (name Tape / qty 100 / price 150), A006 (name Marker / qty 30 / price 120) — and write a bulk UPSERT with ON CONFLICT(sku) DO UPDATE that adds to qty on collision.

③ Finally, place SELECT sku, name, qty FROM stock ORDER BY sku; and confirm A001 / A004 were added to and A006 was newly added.