Upsert (Insert-or-Update) Across PostgreSQL, MySQL, SQLite & SQL Server

🗃️ Code Snippet — July 12, 2026

Upsert (Insert-or-Update) Across PostgreSQL, MySQL, SQLite & SQL Server

When syncing data from APIs, processing event streams, or building ETL pipelines, you need atomic upserts — not SELECT → INSERT/UPDATE (race condition city).

-- PostgreSQL — ON CONFLICT
INSERT INTO products (sku, name, price, updated_at)
VALUES ('ABC-123', 'Widget Pro', 29.99, NOW())
ON CONFLICT (sku)
DO UPDATE SET
    name = EXCLUDED.name,
    price = EXCLUDED.price,
    updated_at = EXCLUDED.updated_at
WHERE products.price <> EXCLUDED.price;
-- ^ WHERE avoids unnecessary writes (saves WAL, triggers)

-- MySQL 8.0+ — ON DUPLICATE KEY
INSERT INTO products (sku, name, price, updated_at)
VALUES ('ABC-123', 'Widget Pro', 29.99, NOW())
AS new
ON DUPLICATE KEY UPDATE
    name = new.name,
    price = new.price;
-- ^ VALUES() is deprecated — use alias

-- SQLite 3.24+ — ON CONFLICT (same as Postgres!)
INSERT INTO products (sku, name, price, updated_at)
VALUES ('ABC-123', 'Widget Pro', 29.99, CURRENT_TIMESTAMP)
ON CONFLICT (sku) DO UPDATE SET
    name = excluded.name,
    price = excluded.price;

-- SQL Server — UPDATE + INSERT (safer than MERGE)
UPDATE products SET name = 'Widget Pro', price = 29.99, updated_at = GETDATE()
WHERE sku = 'ABC-123';
IF @@ROWCOUNT = 0
    INSERT INTO products (sku, name, price, updated_at)
    VALUES ('ABC-123', 'Widget Pro', 29.99, GETDATE());

Quick reference:
| Dialect | Syntax |
|---------|--------|
| PostgreSQL | ON CONFLICT (col) DO UPDATE SET col = EXCLUDED.col |
| MySQL | ON DUPLICATE KEY UPDATE col = alias.col |
| SQLite | ON CONFLICT (col) DO UPDATE SET col = excluded.col |
| SQL Server | UPDATE + INSERT (avoid MERGE — known bugs) |

💡 Bookmark this. ON CONFLICT vs ON DUPLICATE KEY vs MERGE trips up every developer who switches databases.