SQL
Efficiently Inserting or Updating Records (Upsert)
Master the upsert pattern in SQL to atomically insert a new record or update an existing one if a unique key conflict occurs, preventing duplicate data and simplifying logic.
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;
How it works: This snippet demonstrates an 'upsert' operation, which is a combination of an INSERT and an UPDATE. If a row with the `product_id` 'PROD001' already exists, the `ON CONFLICT (product_id) DO UPDATE` clause is triggered. It updates the `name`, `price`, and increments the `stock` by the new stock value provided (`EXCLUDED.stock`). If `product_id` 'PROD001' does not exist, a new row is simply inserted. This syntax is specific to PostgreSQL; MySQL uses `INSERT ... ON DUPLICATE KEY UPDATE` for similar functionality.