SQL
Perform Upsert (Insert or Update on Conflict) in SQL
Learn to efficiently perform an 'upsert' operation in SQL, allowing you to insert a row if it doesn't exist or update it if a conflict occurs, crucial for data synchronization. (PostgreSQL syntax)
INSERT INTO products (product_id, product_name, price)
VALUES (101, 'New Widget', 29.99)
ON CONFLICT (product_id) DO UPDATE SET
product_name = EXCLUDED.product_name,
price = EXCLUDED.price;
How it works: This query demonstrates an 'upsert' operation using PostgreSQL's `ON CONFLICT` clause. It attempts to insert a new row into the `products` table. If a row with the same `product_id` (which must be a unique constraint or primary key) already exists, it detects the conflict and instead updates the `product_name` and `price` of the existing row with the values provided in the `VALUES` clause (referred to as `EXCLUDED`).