SQL
Performing UPSERT Operations (ON CONFLICT / ON DUPLICATE KEY)
Learn how to perform an UPSERT (update or insert) operation in SQL, crucial for preventing duplicate records while ensuring data freshness in your database.
INSERT INTO products (sku, name, price)
VALUES ('SKU001', 'New Gadget', 29.99)
ON CONFLICT (sku) DO UPDATE SET
name = EXCLUDED.name,
price = EXCLUDED.price,
updated_at = NOW();
-- For MySQL, use:
-- INSERT INTO products (sku, name, price) VALUES ('SKU001', 'New Gadget', 29.99)
-- ON DUPLICATE KEY UPDATE name = VALUES(name), price = VALUES(price), updated_at = NOW();
How it works: This query demonstrates an UPSERT operation, which either inserts a new row or updates an existing one. In PostgreSQL (first example), 'ON CONFLICT (sku) DO UPDATE SET' specifies that if a row with the same 'sku' (which should be a unique constraint) already exists, it should update the 'name', 'price', and 'updated_at' fields. The 'EXCLUDED' keyword refers to the values that would have been inserted. The MySQL equivalent is provided as a comment, using 'ON DUPLICATE KEY UPDATE'.