INSERT ... ON DUPLICATE KEY UPDATE

Also Known As

TL;DR

Explanation

ON DUPLICATE KEY UPDATE (ODKU) executes atomically — no race condition between checking existence and inserting. Triggers on any unique key violation (PRIMARY or UNIQUE). VALUES(col) references the value that would have been inserted. REPLACE INTO is similar but deletes then inserts — triggering DELETE + INSERT triggers and resetting AUTO_INCREMENT. Use ODKU for upserts; avoid REPLACE. MySQL 8.0.20+ deprecates VALUES() in favour of aliases: INSERT ... AS new ON DUPLICATE KEY UPDATE col = new.col.

Common Misconception

Why It Matters

Common Mistakes

• Using REPLACE INTO instead of ON DUPLICATE KEY UPDATE — causes unintended cascading deletes via foreign keys.
• Forgetting that ODKU increments the AUTO_INCREMENT counter even when it updates — gaps in IDs appear.
• Using VALUES(col) syntax in MySQL 8.0.20+ — deprecated; use the new row alias syntax instead.

Avoid When

• Do not use REPLACE INTO as a substitute — it deletes and re-inserts, breaking foreign keys.
• Avoid when the distinction between insert and update needs to trigger different application logic — use a transaction with SELECT FOR UPDATE instead.

When To Use

• Use for counters, settings, or any row that should be inserted if absent and updated if present.
• Use when you need an atomic check-then-write with no race condition window.

Code Examples

// Race condition: two requests both pass the SELECT check
$exists = $pdo->prepare('SELECT id FROM settings WHERE user_id=? AND key=?');
$exists->execute([$uid, $key]);
if (!$exists->fetch()) {
    // Another request may have inserted between here and the line below!
    $pdo->prepare('INSERT INTO settings (user_id, key, value) VALUES (?,?,?)')->execute([$uid,$key,$val]);
}

-- Upsert: insert or update view count
INSERT INTO page_views (page_slug, view_count)
VALUES (:slug, 1)
ON DUPLICATE KEY UPDATE view_count = view_count + 1;

-- PHP
$stmt = $pdo->prepare(
    'INSERT INTO user_settings (user_id, `key`, value)
     VALUES (?, ?, ?)
     ON DUPLICATE KEY UPDATE value = VALUES(value)'
);
$stmt->execute([$userId, $key, $value]);

References

• ↗ https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

Tags

Related Terms