SQL
Performing Upsert (Insert or Update) in SQL
Master the upsert operation in SQL to atomically insert a new record or update an existing one based on a unique key, preventing race conditions and simplifying logic.
-- PostgreSQL example
INSERT INTO products (sku, name, price)
VALUES ('SKU001', 'Widget A', 29.99)
ON CONFLICT (sku) DO UPDATE SET
name = EXCLUDED.name,
price = EXCLUDED.price,
updated_at = NOW();
-- MySQL example
INSERT INTO products (sku, name, price)
VALUES ('SKU001', 'Widget A', 29.99)
ON DUPLICATE KEY UPDATE
name = VALUES(name),
price = VALUES(price),
updated_at = NOW();
How it works: This snippet illustrates how to perform an "upsert" operation, which means "insert if not exists, otherwise update." PostgreSQL uses `ON CONFLICT (column) DO UPDATE SET ...` while MySQL uses `ON DUPLICATE KEY UPDATE ...`. This pattern is crucial for maintaining data integrity and simplifying application logic when dealing with unique constraints.