SQL
Perform Atomic Upsert Operations with INSERT ON CONFLICT
Discover how to atomically insert new records or update existing ones in SQL using the UPSERT pattern, preventing data duplication effectively.
-- PostgreSQL / SQLite
INSERT INTO users (email, username, password_hash)
VALUES ('[email protected]', 'john_doe', 'hashed_pass_123')
ON CONFLICT (email) DO UPDATE SET
username = EXCLUDED.username,
password_hash = EXCLUDED.password_hash,
updated_at = NOW();
-- MySQL (using ON DUPLICATE KEY UPDATE)
-- INSERT INTO users (email, username, password_hash)
-- VALUES ('[email protected]', 'jane_doe', 'hashed_pass_456')
-- ON DUPLICATE KEY UPDATE
-- username = VALUES(username),
-- password_hash = VALUES(password_hash),
-- updated_at = NOW();
How it works: This snippet demonstrates the "upsert" operation, which attempts to insert a new row and, if a conflict (e.g., a unique constraint violation) occurs, updates an existing row instead. The PostgreSQL/SQLite example uses ON CONFLICT (column_name) DO UPDATE SET. The commented MySQL example uses ON DUPLICATE KEY UPDATE. This pattern is crucial for data synchronization, preventing duplicate entries, and ensuring data integrity in web applications where concurrent writes are common.