SQL
Perform Upsert Operations with MySQL's ON DUPLICATE KEY UPDATE
Efficiently insert new records or update existing ones in MySQL using the `INSERT ... ON DUPLICATE KEY UPDATE` syntax, avoiding race conditions.
-- Example: Update user's last login if they exist, otherwise insert new user
INSERT INTO users (email, username, last_login, created_at)
VALUES ('[email protected]', 'johndoe', NOW(), NOW())
ON DUPLICATE KEY UPDATE
username = VALUES(username),
last_login = VALUES(last_login),
updated_at = NOW();
-- Requires a UNIQUE index on 'email' or 'username' for ON DUPLICATE KEY UPDATE to trigger.
How it works: This SQL snippet demonstrates an "upsert" operation in MySQL using `INSERT ... ON DUPLICATE KEY UPDATE`. If a row with a conflicting unique key (e.g., `email` or `username` if they have a `UNIQUE` index) already exists, the `UPDATE` clause is executed, modifying the specified columns (`username`, `last_login`, `updated_at`). Otherwise, a new row is inserted. `VALUES(column_name)` refers to the value that would have been inserted for that column.