Data modification & MERGE

Duplicate-source grain, idempotent upsert, DELETE/UPDATE with joins, INSERT-SELECT position risk, transaction batches.

• ★MERGE with a duplicated source key

  A MERGE source with two rows for one target key is undefined territory — some engines error, some silently apply one; pre-aggregate the source to the target's grain, always.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE stock(sku VARCHAR, qty INT); INSERT INTO stock VALUES ('A',10); CREATE TABLE deltas(sku VARCHAR, delta INT); INSERT INTO deltas VALUES ('A',5),('A',3);

  The trap

  MERGE INTO stock USING deltas ON stock.sku=deltas.sku WHEN MATCHED THEN UPDATE SET qty = stock.qty + deltas.delta; SELECT sku, qty FROM stock ORDER BY sku;

  A

  15

  The fix

  MERGE INTO stock USING (SELECT sku, SUM(delta) AS delta FROM deltas GROUP BY sku) d ON stock.sku=d.sku WHEN MATCHED THEN UPDATE SET qty = stock.qty + d.delta; SELECT sku, qty FROM stock ORDER BY sku;

  A

  18

  verification found the silent case is real — the engine applied one of two updates and reported success; loud failure (Postgres) is the good outcome.

• Idempotent upsert

  Run the DML twice — a correct upsert lands the same state both times (bridge to run-level idempotency).

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE daily(windmill VARCHAR, visitors INT);

  The trap

  INSERT INTO daily VALUES ('plum',10); INSERT INTO daily VALUES ('plum',10); SELECT windmill, COUNT(*) AS rows FROM daily GROUP BY windmill;

  plum

  2

  The fix

  INSERT INTO daily SELECT 'plum',10 WHERE NOT EXISTS (SELECT 1 FROM daily WHERE windmill='plum'); INSERT INTO daily SELECT 'plum',10 WHERE NOT EXISTS (SELECT 1 FROM daily WHERE windmill='plum'); SELECT windmill, COUNT(*) AS rows FROM daily GROUP BY windmill;

  plum

  1

  Run twice, the plain INSERT left two duplicate 'plum' rows — a write is idempotent only if a rerun changes nothing, which needs a key and a declared conflict action (ON CONFLICT / MERGE).

• DELETE / UPDATE with joins

  USING / correlated DML scopes which rows die — get the join predicate wrong and you delete too much.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE stock(sku VARCHAR, qty INT); INSERT INTO stock VALUES ('A',10),('B',5); CREATE TABLE disc(sku VARCHAR); INSERT INTO disc VALUES ('B');

  The trap

  DELETE FROM stock; SELECT sku, qty FROM stock ORDER BY sku;

  0 rows

  The fix

  DELETE FROM stock USING disc d WHERE stock.sku = d.sku; SELECT sku, qty FROM stock ORDER BY sku;

  A

  10

  The shrinkage DELETE with no predicate emptied the whole stock table — a joined DELETE scopes which rows die by its predicate, so a missing one deletes everything; DELETE ... USING with the join key removes only the matched rows.

• INSERT ... SELECT column position

  INSERT ... SELECT matches by position, not name — a reordered SELECT silently loads the wrong columns (P07 atom in DML costume).

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE intake(room VARCHAR, item VARCHAR); INSERT INTO intake VALUES ('west','lamp'); CREATE TABLE archive(item VARCHAR, room VARCHAR);

  The trap

  INSERT INTO archive SELECT * FROM intake; SELECT item, room FROM archive;

  west

  lamp

  The fix

  INSERT INTO archive (item, room) SELECT item, room FROM intake; SELECT item, room FROM archive;

  lamp

  west

  The nightly load committed 'west' into the item column — INSERT ... SELECT * mapped intake's columns into archive by position, and the room name is now stored as an item, permanently.

• Transactions at the statement-batch level

  A multi-statement batch's partial-failure visibility depends on the transaction boundary.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE ledger(item VARCHAR, n INT); INSERT INTO ledger VALUES ('cola',3);

  The trap

  DELETE FROM ledger; SELECT COUNT(*) AS rows FROM ledger;

  0

  The fix

  BEGIN; DELETE FROM ledger; ROLLBACK; SELECT COUNT(*) AS rows FROM ledger;

  1

  The bare delete-then-reload published a half state — a DELETE outside a transaction commits immediately, so a failed reload leaves the table empty; wrapping the batch in BEGIN ... ROLLBACK unhappens the attempt and the table keeps its pre-batch rows.

• TRUNCATE vs DELETE

  TRUNCATE is a table reset (fast, whole-table); DELETE is row removal (WHERE-able) — and whether TRUNCATE respects transactions is an engine property.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE t(x INT); INSERT INTO t VALUES (1),(2),(3);

  The trap

  BEGIN; TRUNCATE t; ROLLBACK; SELECT COUNT(*) AS n FROM t;

  3

  The fix

  BEGIN; DELETE FROM t; ROLLBACK; SELECT COUNT(*) AS n FROM t;

  3

  In DuckDB the TRUNCATE inside BEGIN…ROLLBACK restored the rows, but whether TRUNCATE respects transactions is an engine property — on MySQL/Oracle it auto-commits and the rollback can't bring the rows back; DELETE is transactional everywhere.

• Soft-delete filter

  Soft delete moves deletion into data (deleted_at) — every query inherits a mandatory filter, and the filter is a NULL comparison: deleted_at IS NULL, never = NULL.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE designs(name VARCHAR, deleted_at TIMESTAMP); INSERT INTO designs VALUES ('active', NULL),('gilt', TIMESTAMP '2027-01-01');

  The trap

  SELECT name FROM designs WHERE deleted_at = NULL ORDER BY name;

  0 rows

  The fix

  SELECT name FROM designs WHERE deleted_at IS NULL ORDER BY name;

  active

  The active-designs query written as deleted_at = NULL returned zero rows — a comparison with NULL is UNKNOWN, never TRUE, so everything vanished; deleted_at IS NULL keeps the one live design.