UPSERT / MERGE

An upsert updates a row if it already exists and inserts it if it doesn't, in a single atomic operation — avoiding the race condition of a separate "check, then insert-or-update" done in application code.

Oracle and SQL Server implement this with the standard MERGE statement; PostgreSQL and SQLite use INSERT ... ON CONFLICT; MySQL uses INSERT ... ON DUPLICATE KEY UPDATE. All achieve the same goal: the database, not your application, resolves whether a matching row exists — atomically, so two concurrent upserts can't both see "no row exists" and both try to insert, causing a duplicate-key error.

MERGE INTO inventory t
USING (SELECT 'A1' sku, 5 qty FROM dual) s
ON (t.sku = s.sku)
WHEN MATCHED THEN UPDATE SET t.qty = t.qty + s.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (s.sku, s.qty);