SQL
Performing Upsert Operations (INSERT OR UPDATE)
Efficiently insert new records or update existing ones in a database table without explicit checks. Essential for data synchronization and preventing duplicate entries.
-- MySQL / MariaDB Syntax
INSERT INTO products (id, name, price, last_updated)
VALUES (101, 'New Widget', 29.99, NOW())
ON DUPLICATE KEY UPDATE
name = VALUES(name),
price = VALUES(price),
last_updated = VALUES(last_updated);
-- PostgreSQL Syntax (requires unique constraint/primary key on 'id')
INSERT INTO products (id, name, price, last_updated)
VALUES (101, 'New Widget', 29.99, NOW())
ON CONFLICT (id) DO UPDATE SET
name = EXCLUDED.name,
price = EXCLUDED.price,
last_updated = EXCLUDED.last_updated;
How it works: An "upsert" operation allows you to atomically insert a new row if a specified unique key does not exist, or update an existing row if it does. This snippet provides syntax for both MySQL (`ON DUPLICATE KEY UPDATE`) and PostgreSQL (`ON CONFLICT DO UPDATE SET`). It's crucial for maintaining data integrity, like synchronizing product catalogs or user profiles, where you want to add new items or update existing ones based on a unique identifier.