SQL
Performing an UPSERT Operation (INSERT OR UPDATE)
Learn how to efficiently perform an UPSERT operation in SQL, inserting a new row if it doesn't exist or updating it if a conflict occurs, ensuring data integrity for modern web apps.
-- PostgreSQL syntax
INSERT INTO products (product_id, name, price, updated_at)
VALUES (101, 'Laptop', 1200.00, NOW())
ON CONFLICT (product_id) DO UPDATE
SET name = EXCLUDED.name,
price = EXCLUDED.price,
updated_at = NOW();
-- MySQL syntax
INSERT INTO products (product_id, name, price, updated_at)
VALUES (101, 'Laptop', 1200.00, NOW())
ON DUPLICATE KEY UPDATE
name = VALUES(name),
price = VALUES(price),
updated_at = NOW();
How it works: This snippet demonstrates how to perform an "UPSERT" operation, which attempts to INSERT a new row and, if a unique constraint violation occurs (e.g., on `product_id`), it instead UPDATEs the existing row. The PostgreSQL example uses `ON CONFLICT DO UPDATE`, specifying the conflicting column(s) and how to update. The MySQL example uses `ON DUPLICATE KEY UPDATE`, where `VALUES()` refers to the values that would have been inserted. This is crucial for synchronizing data or preventing duplicate entries without separate SELECT and then INSERT/UPDATE logic.