SQL Upsert and Merge Patterns

-- PostgreSQL: INSERT ON CONFLICT (upsert)
INSERT INTO products (sku, name, price, stock)
VALUES ('SKU-001', 'Widget', 19.99, 100)
ON CONFLICT (sku)
DO UPDATE SET
    name = EXCLUDED.name,
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock,
    updated_at = NOW();

-- Bulk upsert
INSERT INTO products (sku, name, price, stock)
VALUES
    ('SKU-001', 'Widget', 19.99, 50),
    ('SKU-002', 'Gadget', 29.99, 30),
    ('SKU-003', 'Gizmo', 9.99, 200)
ON CONFLICT (sku)
DO UPDATE SET
    price = EXCLUDED.price,
    stock = EXCLUDED.stock;

-- ON CONFLICT DO NOTHING (skip duplicates)
INSERT INTO email_log (email, sent_at)
VALUES ('user@example.com', NOW())
ON CONFLICT (email) DO NOTHING;

-- SQL Standard MERGE (SQL Server, Oracle, newer Postgres)
MERGE INTO target_table t
USING source_table s ON t.id = s.id
WHEN MATCHED AND s.updated_at > t.updated_at THEN
    UPDATE SET
        t.name = s.name,
        t.price = s.price,
        t.updated_at = s.updated_at
WHEN NOT MATCHED THEN
    INSERT (id, name, price, updated_at)
    VALUES (s.id, s.name, s.price, s.updated_at)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

-- Upsert with RETURNING (get affected rows)
INSERT INTO counters (key, value)
VALUES ('page_views', 1)
ON CONFLICT (key)
DO UPDATE SET value = counters.value + 1
RETURNING key, value;