SQL
Performing Upsert Operations (INSERT or UPDATE) in SQL
Master upsert operations in SQL to either insert a new row or update an existing one if a conflict (e.g., unique key violation) occurs, ensuring data integrity efficiently.
-- PostgreSQL example (requires a unique constraint on product_id):
INSERT INTO products (product_id, name, price)
VALUES ('PROD001', 'Widget A', 29.99)
ON CONFLICT (product_id) DO UPDATE SET
name = EXCLUDED.name,
price = EXCLUDED.price,
last_updated = NOW();
-- MySQL/MariaDB equivalent (requires a unique constraint on product_id):
INSERT INTO products (product_id, name, price)
VALUES ('PROD001', 'Widget A', 29.99)
ON DUPLICATE KEY UPDATE
name = VALUES(name),
price = VALUES(price),
last_updated = NOW();
How it works: An 'upsert' operation conditionally inserts a new row or updates an existing one based on the presence of a unique key. PostgreSQL uses `ON CONFLICT (column_name) DO UPDATE SET ...`, where `EXCLUDED` refers to the values that would have been inserted. MySQL and MariaDB use `ON DUPLICATE KEY UPDATE ...`, where `VALUES(column_name)` refers to the values specified in the `INSERT` clause. This prevents errors from unique constraint violations and simplifies data synchronization logic.