SQL
Efficiently Perform Upsert Operations for Batch Data
Learn how to insert new records or update existing ones in a single SQL statement, ideal for syncing data and preventing duplicate entries.
-- MySQL/MariaDB specific syntax
INSERT INTO products (product_id, product_name, price, last_updated)
VALUES
(1, 'Laptop', 1200.00, NOW()),
(2, 'Mouse', 25.00, NOW()),
(3, 'Keyboard', 75.00, NOW())
ON DUPLICATE KEY UPDATE
product_name = VALUES(product_name),
price = VALUES(price),
last_updated = VALUES(last_updated);
-- PostgreSQL specific syntax (requires unique constraint)
-- INSERT INTO products (product_id, product_name, price, last_updated)
-- VALUES (1, 'Laptop', 1200.00, NOW())
-- ON CONFLICT (product_id) DO UPDATE
-- SET product_name = EXCLUDED.product_name,
-- price = EXCLUDED.price,
-- last_updated = EXCLUDED.last_updated;
How it works: Upsert operations (UPDATE or INSERT) are common in web development for synchronizing data or handling form submissions where an entry might already exist. This snippet provides examples for both MySQL (`ON DUPLICATE KEY UPDATE`) and PostgreSQL (`ON CONFLICT`). If a row with the specified primary/unique key (e.g., `product_id`) already exists, the `UPDATE` clause is executed; otherwise, a new row is `INSERT`ed. This allows for atomic and efficient handling of data, reducing the need for separate `SELECT` then `INSERT/UPDATE` logic in application code.