How do you write an upsert in SQL?

An upsert inserts a row, or updates it when it already exists. It needs a unique key to detect the conflict, and each engine has its own syntax.

-- PostgreSQL, SQLite
INSERT INTO stock (sku, qty) VALUES ('A1', 5)
ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty;

-- MySQL 8
INSERT INTO stock (sku, qty) VALUES ('A1', 5) AS new
ON DUPLICATE KEY UPDATE qty = new.qty;

The SQL-standard form is MERGE, available in SQL Server, Oracle and PostgreSQL 15+. It is more flexible, with separate branches for matched and not matched rows.

Do not write "select, then insert or update" in application code. Two concurrent requests both see no row and both insert, which gives a duplicate-key error or, without a constraint, duplicate rows. The database upsert is atomic and handles that race.

Use DO NOTHING (or INSERT IGNORE) when you only want to skip existing rows.

An upsert is only as safe as the unique constraint behind it.