SQL
Performing Upsert (Insert or Update) Operations
Learn to efficiently insert new records or update existing ones based on a unique key, preventing duplicates and ensuring data integrity with a single query.
-- PostgreSQL (recommended for new development):
INSERT INTO users (user_id, username, email, last_login)
VALUES (101, 'john_doe', '[email protected]', NOW())
ON CONFLICT (user_id) DO UPDATE SET
username = EXCLUDED.username,
email = EXCLUDED.email,
last_login = EXCLUDED.last_login;
-- MySQL (using ON DUPLICATE KEY UPDATE):
INSERT INTO users (user_id, username, email, last_login)
VALUES (101, 'john_doe', '[email protected]', NOW())
ON DUPLICATE KEY UPDATE
username = VALUES(username),
email = VALUES(email),
last_login = VALUES(last_login);
How it works: This code demonstrates an "upsert" operation, which either inserts a new row if a specified unique key (here, `user_id`) does not exist, or updates an existing row if it does. The PostgreSQL version uses `ON CONFLICT DO UPDATE`, while the MySQL version uses `ON DUPLICATE KEY UPDATE`. This is vital for maintaining data integrity and avoiding duplicate records when importing or synchronizing data.