SQL
Perform Atomic Upsert Operations with MySQL's `INSERT ... ON DUPLICATE KEY UPDATE`
Master MySQL's `INSERT ... ON DUPLICATE KEY UPDATE` to atomically insert a new row or update an existing one if a unique key constraint is violated, ensuring data integrity.
INSERT INTO products (product_id, name, price, stock_quantity, last_updated)
VALUES (101, 'Laptop Pro', 1200.00, 50, NOW())
ON DUPLICATE KEY UPDATE
name = VALUES(name),
price = VALUES(price),
stock_quantity = VALUES(stock_quantity),
last_updated = NOW();
-- Example for PostgreSQL (ON CONFLICT DO UPDATE)
-- INSERT INTO products (product_id, name, price, stock_quantity, last_updated)
-- VALUES (101, 'Laptop Pro', 1200.00, 50, NOW())
-- ON CONFLICT (product_id) DO UPDATE SET
-- name = EXCLUDED.name,
-- price = EXCLUDED.price,
-- stock_quantity = EXCLUDED.stock_quantity,
-- last_updated = NOW();
How it works: The `INSERT ... ON DUPLICATE KEY UPDATE` statement in MySQL provides an atomic way to either insert a new row or update an existing one if a row with the same unique key (e.g., `product_id`) already exists. This prevents errors from duplicate entries and is crucial for maintaining data consistency in web applications. The `VALUES(column_name)` syntax refers to the values specified in the `INSERT` clause. A similar functionality exists in PostgreSQL with `ON CONFLICT DO UPDATE` using `EXCLUDED.column_name`.