Chapter 10 · Changing Data Safely
UPSERT, MERGE, and Conflict Handling
An upsert expresses a business rule for duplicate identity: insert a new entity, ignore a repeated event, or update the existing row. The unique constraint is the arbiter that makes that decision deterministic.
Learning outcomes
Conflict handling requires an identity rule. A UNIQUE constraint or index defines which proposed row conflicts with which stored row; the statement then chooses to reject, ignore, update, or replace.
Use SQLite ON CONFLICT DO NOTHING and DO UPDATE.
Reference proposed values through the excluded pseudo-table.
Make upserts conditional so older or identical data does not overwrite newer state.
Distinguish UPSERT from destructive REPLACE behavior.
Compare PostgreSQL, MySQL, SQL Server, and Oracle conflict syntax at a conceptual level.
The unique key is the arbiter
Conflict handling is deterministic only when the business identity is enforced by a unique constraint or index.
Ignore a duplicate safely
INSERT INTO change_request ( request_key, operation)VALUES ( 'customer-import-2026-08-05', 'load staged customers')ON CONFLICT (request_key) DO NOTHINGRETURNING request_key, applied_at;The first execution inserts one row. A retry returns no row because the stable request key already exists. The caller must distinguish “inserted now” from “already applied.”
Update the existing row on conflict
INSERT INTO product ( sku, product_name, unit_price, stock_qty, updated_at)VALUES ( 'SQL-FOUND', 'Database Foundations', 52.00, 31, '2026-08-05 06:00:00')ON CONFLICT (sku) DO UPDATE SET product_name = excluded.product_name, unit_price = excluded.unit_price, stock_qty = excluded.stock_qty, updated_at = excluded.updated_atRETURNING product_id, sku, unit_price, stock_qty, updated_at;excluded represents the proposed row that could not be inserted because of the selected uniqueness conflict.
Do not overwrite newer data
INSERT INTO product ( sku, product_name, unit_price, stock_qty, updated_at)SELECT sku, product_name, unit_price, stock_qty, source_timeFROM product_feedWHERE trueON CONFLICT (sku) DO UPDATE SET product_name = excluded.product_name, unit_price = excluded.unit_price, stock_qty = excluded.stock_qty, updated_at = excluded.updated_atWHERE excluded.updated_at > product.updated_atRETURNING product_id, sku, updated_at;The final WHERE filters the DO UPDATE action. A conflicting row with an older or equal source timestamp is left unchanged.
REPLACE is not an ordinary update
CREATE TEMP TABLE replace_demo ( row_id INTEGER PRIMARY KEY, natural_key TEXT NOT NULL UNIQUE, payload TEXT NOT NULL) STRICT;INSERT INTO replace_demo (natural_key, payload)VALUES ('A', 'first');SELECT row_id, natural_key, payloadFROM replace_demo;-- REPLACE removes the conflicting row and inserts another row.REPLACE INTO replace_demo (natural_key, payload)VALUES ('A', 'second');SELECT row_id, natural_key, payloadFROM replace_demo;SQLite REPLACE uses conflict-replacement semantics, not an in-place UPDATE guarantee. Use ON CONFLICT DO UPDATE when preserving row identity and relationships matters.
UPSERT and MERGE across engines
| Engine | Common form | Important distinction |
|---|---|---|
| SQLite | INSERT ... ON CONFLICT ... DO UPDATE | PostgreSQL-inspired syntax; supports multiple conflict clauses. |
| PostgreSQL | INSERT ... ON CONFLICT or MERGE | ON CONFLICT is designed around unique/exclusion arbiters; MERGE is broader conditional matching. |
| MySQL | INSERT ... ON DUPLICATE KEY UPDATE | Conflict selection follows MySQL unique-index behavior and syntax. |
| SQL Server | MERGE or separate DML patterns | MERGE combines source/target actions; concurrency and duplicate-source assumptions require careful design. |
| Oracle | MERGE | Matches a source to a target and chooses matched or not-matched actions. |
Portable MERGE shape
MERGE INTO product AS targetUSING product_feed AS sourceON target.sku = source.skuWHEN MATCHED THEN UPDATE SET product_name = source.product_name, unit_price = source.unit_price, stock_qty = source.stock_qtyWHEN NOT MATCHED THEN INSERT (sku, product_name, unit_price, stock_qty) VALUES (source.sku, source.product_name, source.unit_price, source.stock_qty);MERGE capabilities, concurrency guarantees, RETURNING/OUTPUT support, and permitted clauses differ materially. Treat the conceptual shape as portable, not the exact text.
Conflict-handling checklist
Identity
Which enforced unique key means “the same entity or event”?
Policy
Should duplicates fail, be ignored, or update selected fields?
Ordering
Can an older retry overwrite newer state?
Outcome
How does the caller know whether INSERT, UPDATE, or no-op occurred?
Reusable Chapter 10 practice database
Run this setup once in a disposable SQLite database. Every lesson uses the same constrained tables so that write behavior, references, conflicts, and verification can be compared consistently.
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS change_request;DROP TABLE IF EXISTS order_archive;DROP TABLE IF EXISTS order_item;DROP TABLE IF EXISTS sales_order;DROP TABLE IF EXISTS product_feed;DROP TABLE IF EXISTS customer_stage;DROP TABLE IF EXISTS product;DROP TABLE IF EXISTS customer;CREATE TABLE customer ( customer_id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE, full_name TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'paused', 'closed')), loyalty_points INTEGER NOT NULL DEFAULT 0 CHECK (loyalty_points >= 0), deleted_at TEXT, updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;CREATE TABLE product ( product_id INTEGER PRIMARY KEY, sku TEXT NOT NULL UNIQUE, product_name TEXT NOT NULL, unit_price REAL NOT NULL CHECK (unit_price >= 0), stock_qty INTEGER NOT NULL DEFAULT 0 CHECK (stock_qty >= 0), discontinued_at TEXT, updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;CREATE TABLE sales_order ( order_id INTEGER PRIMARY KEY, external_ref TEXT NOT NULL UNIQUE, customer_id INTEGER NOT NULL REFERENCES customer(customer_id), status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'paid', 'cancelled', 'refunded')), total_amount REAL NOT NULL DEFAULT 0 CHECK (total_amount >= 0), ordered_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;CREATE TABLE order_item ( order_id INTEGER NOT NULL REFERENCES sales_order(order_id) ON DELETE CASCADE, line_no INTEGER NOT NULL, product_id INTEGER NOT NULL REFERENCES product(product_id), quantity INTEGER NOT NULL CHECK (quantity > 0), unit_price REAL NOT NULL CHECK (unit_price >= 0), PRIMARY KEY (order_id, line_no)) STRICT;CREATE TABLE customer_stage ( email TEXT NOT NULL UNIQUE, full_name TEXT NOT NULL, status TEXT NOT NULL CHECK (status IN ('active', 'paused', 'closed'))) STRICT;CREATE TABLE product_feed ( sku TEXT NOT NULL UNIQUE, product_name TEXT NOT NULL, unit_price REAL NOT NULL CHECK (unit_price >= 0), stock_qty INTEGER NOT NULL CHECK (stock_qty >= 0), source_time TEXT NOT NULL) STRICT;CREATE TABLE order_archive ( order_id INTEGER PRIMARY KEY, external_ref TEXT NOT NULL, customer_id INTEGER NOT NULL, status TEXT NOT NULL, total_amount REAL NOT NULL, ordered_at TEXT NOT NULL, archived_at TEXT NOT NULL) STRICT;CREATE TABLE change_request ( request_key TEXT PRIMARY KEY, operation TEXT NOT NULL, applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;INSERT INTO customer (customer_id, email, full_name, status, loyalty_points, updated_at)VALUES (1, 'nadia@example.com', 'Nadia Rahimi', 'active', 120, '2026-08-01 09:00:00'), (2, 'omar@example.com', 'Omar Haddad', 'active', 80, '2026-08-01 09:00:00'), (3, 'lina@example.com', 'Lina Chen', 'paused', 40, '2026-08-01 09:00:00'), (4, 'ava@example.com', 'Ava Morgan', 'active', 0, '2026-08-01 09:00:00'), (5, 'noah@example.com', 'Noah Silva', 'closed', 15, '2026-08-01 09:00:00');INSERT INTO product (product_id, sku, product_name, unit_price, stock_qty, updated_at)VALUES (10, 'SQL-FOUND', 'Database Foundations', 49.00, 35, '2026-08-01 09:00:00'), (11, 'SQL-QUERY', 'SQL Query Practice', 69.00, 18, '2026-08-01 09:00:00'), (12, 'SQL-CARD', 'SQL Reference Card', 15.00, 80, '2026-08-01 09:00:00'), (13, 'SQL-LAB', 'SQLite Lab Bundle', 29.00, 22, '2026-08-01 09:00:00'), (14, 'DQ-WORK', 'Data Quality Workbook', 24.50, 0, '2026-08-01 09:00:00');INSERT INTO sales_order (order_id, external_ref, customer_id, status, total_amount, ordered_at)VALUES (100, 'WEB-100', 1, 'paid', 79.00, '2026-07-01 10:00:00'), (101, 'WEB-101', 2, 'draft', 0.00, '2026-07-10 11:00:00'), (102, 'WEB-102', 3, 'cancelled', 29.00, '2025-12-01 12:00:00'), (103, 'WEB-103', 4, 'paid', 69.00, '2026-08-01 13:00:00'), (104, 'WEB-104', 5, 'refunded', 15.00, '2025-11-15 14:00:00');INSERT INTO order_item VALUES (100, 1, 10, 1, 49.00), (100, 2, 13, 1, 30.00), (102, 1, 13, 1, 29.00), (103, 1, 11, 1, 69.00), (104, 1, 12, 1, 15.00);INSERT INTO customer_stage VALUES ('marta@example.com', 'Marta Costa', 'active'), ('reza@example.com', 'Reza Nouri', 'active'), ('lina@example.com', 'Lina Chen', 'active');INSERT INTO product_feed VALUES ('SQL-FOUND', 'Database Foundations', 52.00, 31, '2026-08-05 06:00:00'), ('SQL-CARD', 'SQL Reference Card', 15.00, 75, '2026-08-05 06:00:00'), ('SQL-OPS', 'Database Operations', 89.00, 12, '2026-08-05 06:00:00');The setup drops tables. Never execute training reset scripts against a production database.
Practice lab
- Register one request key twice with DO NOTHING.
- Upsert all product_feed rows into product.
- Ensure an older feed row cannot overwrite a newer product.
- Compare product_id before and after REPLACE in a disposable transaction.
- Explain why a UNIQUE SKU is required for this workflow.
INSERT INTO product ( sku, product_name, unit_price, stock_qty, updated_at)SELECT sku, product_name, unit_price, stock_qty, source_timeFROM product_feedWHERE trueON CONFLICT (sku) DO UPDATE SET product_name = excluded.product_name, unit_price = excluded.unit_price, stock_qty = excluded.stock_qty, updated_at = excluded.updated_atWHERE excluded.updated_at > product.updated_at;Checkpoint
Resolve conflicts deliberately
- What database object identifies a conflict?
- What does excluded mean in SQLite UPSERT?
- Why add a freshness predicate to DO UPDATE?
- Why is REPLACE risky for referenced rows?
- Is MERGE text fully portable across vendors?
Review the answers
A unique constraint or index identifies conflict. excluded is the proposed row. A freshness condition prevents stale overwrites. REPLACE can delete and reinsert, changing identity and relationships. MERGE is conceptually common but syntactically and behaviorally vendor-specific.
Summary and references
- Upsert correctness begins with an enforced business identity.
- DO NOTHING supports idempotent retries; DO UPDATE synchronizes chosen attributes.
- Conditional updates protect newer state from stale inputs.
- REPLACE and MERGE require separate semantic review.