SQL
Perform an Upsert (Insert or Update) on a Single Record
Master the SQL UPSERT operation using PostgreSQL's INSERT ... ON CONFLICT DO UPDATE to either insert a new record or update an existing one atomically.
INSERT INTO products (product_id, name, price, stock)
VALUES ('PROD001', 'Widget A', 29.99, 100)
ON CONFLICT (product_id) DO UPDATE SET
name = EXCLUDED.name,
price = EXCLUDED.price,
stock = products.stock + EXCLUDED.stock; -- Example: increment stock
-- Note: This syntax is specific to PostgreSQL. MySQL uses ON DUPLICATE KEY UPDATE.
How it works: This SQL snippet demonstrates an "upsert" operation, which atomically inserts a new record if it doesn't exist, or updates it if it does. This specific syntax is for PostgreSQL. The `ON CONFLICT (product_id)` clause specifies that if a row with the given `product_id` already exists, the `DO UPDATE SET` action should be performed. `EXCLUDED.column_name` refers to the values that would have been inserted had there been no conflict. This pattern is invaluable for maintaining data consistency and idempotency in applications.