SQL
Efficiently Handle Data Inserts or Updates with UPSERT
Learn to perform an atomic "UPSERT" operation in SQL, either inserting a new row or updating an existing one based on unique constraints, crucial for web app data management.
-- PostgreSQL (>= 9.5)
INSERT INTO users (email, username, created_at, updated_at)
VALUES ('[email protected]', 'testuser', NOW(), NOW())
ON CONFLICT (email) DO UPDATE SET
username = EXCLUDED.username,
updated_at = NOW();
-- MySQL (>= 8.0)
INSERT INTO products (product_id, name, price, stock)
VALUES (101, 'New Widget', 29.99, 100)
ON DUPLICATE KEY UPDATE
name = VALUES(name),
price = VALUES(price),
stock = VALUES(stock);
How it works: This snippet demonstrates how to perform an "UPSERT" operation, which either inserts a new row if it doesn't exist or updates an existing one if a unique constraint (like email or product_id) is violated. This is crucial for web applications to manage data without needing separate SELECT and INSERT/UPDATE statements, reducing race conditions and improving efficiency. The syntax varies across database systems, with ON CONFLICT DO UPDATE for PostgreSQL and ON DUPLICATE KEY UPDATE for MySQL.