SQL
Perform Upsert (Insert or Update) Operations
Master the upsert pattern in SQL to either insert new records or update existing ones based on a unique constraint, preventing data duplication.
-- PostgreSQL / SQLite example (INSERT ... ON CONFLICT)
INSERT INTO products (product_id, name, price, last_updated)
VALUES ('P001', 'Laptop Pro', 1200.00, NOW())
ON CONFLICT (product_id) DO UPDATE SET
name = EXCLUDED.name,
price = EXCLUDED.price,
last_updated = EXCLUDED.last_updated;
-- MySQL 8.0+ example (INSERT ... ON DUPLICATE KEY UPDATE)
-- INSERT INTO products (product_id, name, price, last_updated)
-- VALUES ('P002', 'Monitor Ultra', 300.00, NOW())
-- ON DUPLICATE KEY UPDATE
-- name = VALUES(name),
-- price = VALUES(price),
-- last_updated = VALUES(last_updated);
-- SQL Server example (MERGE statement)
-- MERGE INTO products AS target
-- USING (VALUES ('P003', 'Keyboard Mech', 150.00, GETDATE())) AS source (product_id, name, price, last_updated)
-- ON target.product_id = source.product_id
-- WHEN MATCHED THEN
-- UPDATE SET
-- name = source.name,
-- price = source.price,
-- last_updated = source.last_updated
-- WHEN NOT MATCHED THEN
-- INSERT (product_id, name, price, last_updated)
-- VALUES (source.product_id, source.name, source.price, source.last_updated);
How it works: An "upsert" operation either inserts a new row if a specified unique key doesn't exist or updates an existing row if the key does exist. This snippet provides examples for different SQL databases (PostgreSQL, MySQL, SQL Server) demonstrating how to achieve this. It's crucial for syncing data, importing, or managing unique records without encountering primary key violation errors, ensuring data integrity.