SQL
Perform an Upsert Operation (Insert or Update) in SQL
Master the upsert pattern in SQL to either insert new records or update existing ones based on a unique key, preventing duplicate entries and ensuring data integrity.
-- PostgreSQL syntax:
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 = EXCLUDED.stock_quantity;
-- MySQL syntax (commented out for primary snippet, but useful to know):
-- INSERT INTO products (product_id, product_name, price, stock_quantity)
-- VALUES (101, 'New Widget', 29.99, 150)
-- ON DUPLICATE KEY UPDATE
-- product_name = VALUES(product_name),
-- price = VALUES(price),
-- stock_quantity = VALUES(stock_quantity);
How it works: The 'upsert' operation allows you to either insert a new row or update an existing one if a row with a matching unique key already exists. The PostgreSQL syntax uses `ON CONFLICT (column_name) DO UPDATE SET ...`, referencing `EXCLUDED` to get the values that would have been inserted. MySQL uses `ON DUPLICATE KEY UPDATE ...`, referencing `VALUES()` for the proposed new values. This is essential for maintaining data integrity and simplifying data synchronization logic.