SQL
SQL Query: Perform UPSERT Operations with INSERT ON CONFLICT
Efficiently insert new records or update existing ones in a single SQL statement using the `INSERT ... ON CONFLICT DO UPDATE` (UPSERT) clause for data synchronization.
INSERT INTO
products (product_id, product_name, price, stock_quantity)
VALUES
(101, 'New Widget', 29.99, 150)
ON CONFLICT (product_id) DO UPDATE SET
product_name = EXCLUDED.product_name,
price = EXCLUDED.price,
stock_quantity = products.stock_quantity + EXCLUDED.stock_quantity; -- Add to stock
-- OR: stock_quantity = EXCLUDED.stock_quantity; -- Replace stock
How it works: This snippet demonstrates an "UPSERT" operation using `INSERT ... ON CONFLICT DO UPDATE`, common in PostgreSQL and SQLite. It attempts to insert a new row into the `products` table. If a row with the specified `product_id` already exists (due to a unique constraint), the `ON CONFLICT` clause triggers, updating the existing row's `product_name`, `price`, and `stock_quantity`. `EXCLUDED` refers to the values that would have been inserted had there been no conflict. This is ideal for synchronizing data or preventing duplicate entries while updating relevant fields.