SQL
Perform Upsert (Insert or Update) Operation in SQL
Learn to efficiently insert a new record or update an existing one if a conflict occurs on a unique constraint, a common pattern for data synchronization.
INSERT INTO products (sku, name, price, stock)
VALUES ('PROD123', 'Widget X', 29.99, 100)
ON CONFLICT (sku) DO UPDATE SET
name = EXCLUDED.name,
price = EXCLUDED.price,
stock = products.stock + EXCLUDED.stock;
How it works: This snippet demonstrates an 'UPSERT' operation, common in PostgreSQL (syntax may vary for other databases). It attempts to 'INSERT' a new product. If a conflict arises because a record with the same 'sku' (which is presumed to be a unique constraint) already exists, it executes the 'DO UPDATE SET' clause instead. 'EXCLUDED.name' refers to the value that would have been inserted, allowing you to update specific columns or even increment existing values like 'stock'.