How to Write a SQL UPSERT
How to Write a SQL UPSERT (Compared Across MySQL, PostgreSQL, and BigQuery) An "UPSERT" — update if a row exists, insert if it doesn't — is one of those operations every database supports, but with completely different syntax. If you've moved between databases, you've probably had to relearn this every time. Here's all three side by side. 📘 Why UPSERT Matters Without UPSERT, handling "insert or update" logic means checking for existence first, then branching — two round trips and a race condition risk under concurrent writes. UPSERT does it atomically, in one statement. 🧪 MySQL: INSERT ... ON DUPLICATE KEY UPDATE INSERT INTO inventory (product_id, quantity) VALUES (101, 50) ON DUPLICATE KEY UPDATE quantity = quantity + 50; Requires a UNIQUE or PRIMARY KEY constraint on product_id for MySQL to know what counts as a "duplicate." 🧪 PostgreSQL: INSERT ... ON CONFLICT DO UPDATE INSERT INTO inventory (product_id, quantity) VALUES ...