SQL
Upserting Records (Insert or Update on Conflict)
Discover how to perform an 'upsert' operation, inserting a new record if it doesn't exist or updating it if a unique constraint conflict occurs, streamlining data synchronization.
INSERT INTO products (sku, name, price, stock)
VALUES ('PROD001', 'Widget A', 29.99, 100)
ON CONFLICT (sku) DO UPDATE SET
name = EXCLUDED.name,
price = EXCLUDED.price,
stock = products.stock + EXCLUDED.stock;
How it works: This SQL snippet shows an "upsert" operation, a common pattern to either insert a new row or update an existing one if a unique constraint is violated. In PostgreSQL, ON CONFLICT (column_name) DO UPDATE SET is used. EXCLUDED refers to the row that would have been inserted. MySQL uses ON DUPLICATE KEY UPDATE syntax for a similar effect.