SQL
Perform an Upsert Operation (Insert or Update on Conflict)
Efficiently insert new rows or update existing ones if a unique key conflict occurs, using database-specific syntax like ON CONFLICT or ON DUPLICATE KEY UPDATE.
-- PostgreSQL syntax
INSERT INTO users (email, username, last_login)
VALUES ('[email protected]', 'testuser', NOW())
ON CONFLICT (email) DO UPDATE SET
username = EXCLUDED.username,
last_login = EXCLUDED.last_login;
-- MySQL syntax equivalent:
-- INSERT INTO users (email, username, last_login)
-- VALUES ('[email protected]', 'testuser', NOW())
-- ON DUPLICATE KEY UPDATE
-- username = VALUES(username),
-- last_login = VALUES(last_login);
How it works: This snippet demonstrates an 'upsert' operation, which attempts to insert a row and, if a unique constraint conflict occurs (e.g., on the `email` column), updates the existing row instead. The example shows PostgreSQL's `ON CONFLICT DO UPDATE SET` and notes the MySQL equivalent `ON DUPLICATE KEY UPDATE`. This is crucial for maintaining data integrity and idempotency, ensuring that a record is either created or updated without error if it already exists.