Intermediate LevelSQL & MySQL
How does UPSERT work using INSERT ... ON DUPLICATE KEY UPDATE in MySQL?
❓ Interview Prompt: Explain UPSERT, MySQL `INSERT ... ON DUPLICATE KEY UPDATE`, PostgreSQL `ON CONFLICT (...) DO UPDATE`, and atomic insert-or-update execution.
An UPSERT atomically inserts a new row if it does not exist, or updates the existing row if a Primary Key or UNIQUE constraint conflict occurs:
Syntax Variants:
•
MySQL: 'INSERT INTO table (id, val) VALUES (1, 'A') ON DUPLICATE KEY UPDATE val = VALUES(val);'
•
PostgreSQL: 'INSERT INTO table (id, val) VALUES (1, 'A') ON CONFLICT (id) DO UPDATE SET val = EXCLUDED.val;'
Code Implementation Example
sql
-- MySQL Atomic UPSERT statement
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (101, 1, NOW())
ON DUPLICATE KEY UPDATE
login_count = login_count + 1,
last_login = VALUES(last_login);💡Executing atomic UPSERTs with ON DUPLICATE KEY UPDATE.
🎯 Interview Key Takeaway
Use UPSERT syntax to perform atomic insert-or-update operations without risking race conditions.
#SQL#UPSERT#ON DUPLICATE KEY#ON CONFLICT#Intermediate
Back to all SQL & MySQL Questions→