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 (101, 50)
ON CONFLICT (product_id)
DO UPDATE SET quantity = inventory.quantity + EXCLUDED.quantity;

PostgreSQL uses EXCLUDED to reference the row that would have been inserted — useful when the update needs to reference the new incoming value alongside the existing one.

🧪 BigQuery: MERGE

MERGE inventory AS T
USING (SELECT 101 AS product_id, 50 AS quantity) AS S
ON T.product_id = S.product_id
WHEN MATCHED THEN
  UPDATE SET T.quantity = T.quantity + S.quantity
WHEN NOT MATCHED THEN
  INSERT (product_id, quantity) VALUES (S.product_id, S.quantity);

BigQuery doesn't have a dedicated UPSERT keyword — MERGE is the general-purpose tool, and it's also what you'd use for more complex conditional logic (including deletes) beyond simple upsert.

🎯 Quick Reference

Database	Syntax
MySQL	INSERT ... ON DUPLICATE KEY UPDATE
PostgreSQL	INSERT ... ON CONFLICT DO UPDATE
BigQuery	MERGE ... WHEN MATCHED / WHEN NOT MATCHED
MSSQL	MERGE (similar syntax to BigQuery's)

🔍 Handling Batch Upserts

All three support upserting multiple rows at once rather than one at a time — for BigQuery specifically, this is where MERGE really earns its keep, since you can upsert an entire staging table in a single statement:

MERGE inventory AS T
USING staging_inventory AS S
ON T.product_id = S.product_id
WHEN MATCHED THEN UPDATE SET T.quantity = S.quantity
WHEN NOT MATCHED THEN INSERT (product_id, quantity) VALUES (S.product_id, S.quantity);

⚠️ Common Mistakes

• Forgetting the UNIQUE/PRIMARY KEY constraint that MySQL and PostgreSQL rely on to detect conflicts — without it, these statements just do a plain INSERT every time
• In PostgreSQL, referencing the wrong table alias instead of EXCLUDED when trying to access the incoming row's values
• Using MERGE for a simple single-row upsert in BigQuery when the logic doesn't actually need MERGE's full conditional power — still correct, just more verbose than necessary

🔗 Related Posts

• BigQuery MERGE Statement – Explained
• PostgreSQL ON CONFLICT Statement – Explained