SQL
Perform Upsert (Insert or Update) Operations in SQL
Master the upsert pattern in SQL to efficiently insert a new record if it doesn't exist, or update an existing one, crucial for maintaining unique data.
-- PostgreSQL / SQLite syntax
INSERT INTO products (id, name, price)
VALUES (1, 'Laptop', 1200.00)
ON CONFLICT (id) DO UPDATE SET
name = EXCLUDED.name,
price = EXCLUDED.price;
-- MySQL syntax (requires unique key on 'id')
-- INSERT INTO products (id, name, price)
-- VALUES (1, 'Laptop', 1200.00)
-- ON DUPLICATE KEY UPDATE
-- name = VALUES(name),
-- price = VALUES(price);
How it works: This snippet demonstrates the 'upsert' operation, which attempts to insert a row. If a unique constraint (like 'id') is violated, it instead updates the existing row with the new values. The PostgreSQL 'ON CONFLICT DO UPDATE SET' syntax is shown, along with a commented MySQL 'ON DUPLICATE KEY UPDATE' example, both achieving the same result.