SQL UPSERT: INSERT ON CONFLICT and MERGE

PostgreSQL uses INSERT ... ON CONFLICT to handle upserts. The ON CONFLICT clause specifies which constraint or columns determine whether a row already exists. DO UPDATE SET defines which columns to update when a conflict is detected. DO NOTHING skips the insert silently. The EXCLUDED pseudo-table references the values that were proposed for insertion.

-- Upsert a product: insert if new, update if exists
INSERT INTO products (product_id, name, price, updated_at)
VALUES (101, 'Widget Pro', 29.99, NOW())
ON CONFLICT (product_id)
DO UPDATE SET
  name = EXCLUDED.name,
  price = EXCLUDED.price,
  updated_at = EXCLUDED.updated_at;

-- EXCLUDED refers to the row that was attempted to insert
-- product_id is the conflict target (must have a unique constraint)

-- Bulk upsert from a staging table
INSERT INTO dim_customers (customer_id, name, email, updated_at)
SELECT customer_id, name, email, NOW()
FROM staging_customers
ON CONFLICT (customer_id)
DO UPDATE SET
  name = EXCLUDED.name,
  email = EXCLUDED.email,
  updated_at = EXCLUDED.updated_at
WHERE dim_customers.name != EXCLUDED.name
   OR dim_customers.email != EXCLUDED.email;
-- WHERE clause: only update if something actually changed

MERGE is the ANSI SQL standard for upsert operations. It matches rows from a source table against a target table using an ON condition. You define separate actions for WHEN MATCHED (update or delete) and WHEN NOT MATCHED (insert). MERGE can handle insert, update, and delete in a single statement, making it the most flexible upsert syntax.

-- SQL Server / Snowflake MERGE syntax
MERGE INTO dim_products AS target
USING staging_products AS source
  ON target.product_id = source.product_id

WHEN MATCHED AND source.is_deleted = 1 THEN
  DELETE

WHEN MATCHED THEN
  UPDATE SET
    target.name = source.name,
    target.price = source.price,
    target.updated_at = CURRENT_TIMESTAMP

WHEN NOT MATCHED THEN
  INSERT (product_id, name, price, created_at, updated_at)
  VALUES (source.product_id, source.name, source.price,
          CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

MySQL uses INSERT ... ON DUPLICATE KEY UPDATE for upserts. When an insert violates a primary key or unique index constraint, MySQL executes the UPDATE clause instead. The VALUES() function references the values that were proposed for insertion (similar to PostgreSQL EXCLUDED). In MySQL 8.0.19+, use the alias syntax instead of VALUES().

-- MySQL upsert
INSERT INTO products (product_id, name, price)
VALUES (101, 'Widget Pro', 29.99)
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  price = VALUES(price);

-- MySQL 8.0.19+ alias syntax (preferred)
INSERT INTO products (product_id, name, price)
VALUES (101, 'Widget Pro', 29.99) AS new_row
ON DUPLICATE KEY UPDATE
  name = new_row.name,
  price = new_row.price;

-- Bulk upsert
INSERT INTO inventory (sku, warehouse_id, quantity)
VALUES
  ('A001', 1, 50),
  ('A002', 1, 30),
  ('A001', 2, 20)
ON DUPLICATE KEY UPDATE
  quantity = VALUES(quantity);

SCD Type 1 overwrites the old value with the new value. No history is preserved. UPSERT is a natural fit: if the dimension row exists, update it; if not, insert it. This is the simplest SCD type and the most common use case for UPSERT in dimensional data warehouses. The updated_at timestamp tracks when the row last changed.

-- SCD Type 1: overwrite with latest values
-- PostgreSQL
INSERT INTO dim_customers (
  customer_id, name, email, city, updated_at
)
SELECT customer_id, name, email, city, NOW()
FROM staging_customers
ON CONFLICT (customer_id)
DO UPDATE SET
  name = EXCLUDED.name,
  email = EXCLUDED.email,
  city = EXCLUDED.city,
  updated_at = EXCLUDED.updated_at;

-- Only update rows where something changed (optimization)
-- Add a WHERE clause to skip no-op updates:
-- WHERE dim_customers.name != EXCLUDED.name
--    OR dim_customers.email != EXCLUDED.email
--    OR dim_customers.city != EXCLUDED.city;

Sometimes you want to update only specific columns or only update when certain conditions are met. For example, update the price only if the incoming price is newer (based on a timestamp). Or update the status only if it is moving forward in a state machine. Both PostgreSQL and MERGE support WHERE conditions on the update clause.

-- PostgreSQL: only update if incoming data is newer
INSERT INTO product_prices (product_id, price, effective_date)
VALUES (101, 34.99, '2025-03-15')
ON CONFLICT (product_id)
DO UPDATE SET
  price = EXCLUDED.price,
  effective_date = EXCLUDED.effective_date
WHERE EXCLUDED.effective_date > product_prices.effective_date;

-- Snowflake MERGE: update status only if progressing forward
MERGE INTO orders AS target
USING staging_orders AS source
  ON target.order_id = source.order_id
WHEN MATCHED
  AND source.status_rank > target.status_rank THEN
  UPDATE SET
    target.status = source.status,
    target.status_rank = source.status_rank,
    target.updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
  INSERT (order_id, status, status_rank, created_at, updated_at)
  VALUES (source.order_id, source.status, source.status_rank,
          CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);