SQL
Performing an Upsert (Insert or Update) Operation in SQL
Learn how to perform an "upsert" operation in SQL, which inserts a new record if it doesn't exist, or updates an existing one if it does, preventing duplicates and ensuring data integrity.
-- PostgreSQL Example:
INSERT INTO products (product_id, name, price, stock)
VALUES ('P001', 'Laptop', 1200.00, 50)
ON CONFLICT (product_id) DO UPDATE SET
name = EXCLUDED.name,
price = EXCLUDED.price,
stock = EXCLUDED.stock;
-- MySQL Example (alternative, commonly used):
INSERT INTO products (product_id, name, price, stock)
VALUES ('P002', 'Keyboard', 75.00, 150)
ON DUPLICATE KEY UPDATE
name = VALUES(name),
price = VALUES(price),
stock = VALUES(stock);
How it works: The "upsert" operation allows you to atomically insert a row if it doesn't exist, or update it if a conflict occurs on a unique constraint (like `product_id`). PostgreSQL uses `ON CONFLICT (constraint_column) DO UPDATE SET ...`, where `EXCLUDED` refers to the values that would have been inserted. MySQL uses `ON DUPLICATE KEY UPDATE ...`, where `VALUES(column_name)` refers to the values provided in the `INSERT` statement. This is crucial for maintaining data consistency in web applications.