When should you NOT use INSERT ... ON DUPLICATE KEY UPDATE?

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 is INSERT ... ON DUPLICATE KEY UPDATE the right choice?

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.

← Back to glossary

INSERT ... ON DUPLICATE KEY UPDATE

Database PHP 5.1+ Intermediate

debt(d7/e3/b3/t7)

d7 Detectability Operational debt — how invisible misuse is to your safety net

Closest to 'only careful code review or runtime testing' (d7). The detection_hints confirm automated detection is 'no', and the code_pattern (SELECT then INSERT without transaction) requires a reviewer to recognize the race condition or the misuse of REPLACE INTO — no standard linter or SAST tool in the PHP/MySQL ecosystem reliably flags this. It surfaces only under concurrency at runtime or via deliberate code review.

e3 Effort Remediation debt — work required to fix once spotted

Closest to 'simple parameterised fix' (e3). The quick_fix is to replace the SELECT+INSERT pattern (or REPLACE INTO misuse) with INSERT ... ON DUPLICATE KEY UPDATE. This is a targeted SQL rewrite confined to the affected query sites — more than a trivial one-liner because you must also adjust the UPDATE clause and possibly handle column aliasing in MySQL 8.0.20+, but it stays within a single component or DAO layer.

b3 Burden Structural debt — long-term weight of choosing wrong

Closest to 'localised tax' (b3). The choice applies to specific upsert query sites within web/cli contexts. Once the correct ODKU syntax is in place, it doesn't impose ongoing cost across the rest of the codebase. The AUTO_INCREMENT gap side-effect and the VALUES() deprecation are nuisances but don't propagate beyond the query layer.

t7 Trap Cognitive debt — how counter-intuitive correct behaviour is

Closest to 'serious trap' (t7). The misconception is explicit: REPLACE INTO appears to be a safe, equivalent alternative but silently deletes the old row, resets auto-increment IDs, fires DELETE triggers, and cascades foreign key deletes. This contradicts how a developer familiar with 'upsert' semantics elsewhere would expect the operation to behave — the 'obvious' alternative is destructive in ways most developers won't anticipate without prior exposure to the gotcha.

About DEBT scoring → scored by claude-sonnet-4-6 · 2026-05-10 · reviewed by human

Also Known As

MySQL upsert ON DUPLICATE KEY UPDATE INSERT OR UPDATE MySQL ODKU

TL;DR

An atomic MySQL upsert — inserts a new row or updates the existing one if a unique key constraint would be violated.

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

✗ REPLACE INTO is a safe alternative to ON DUPLICATE KEY UPDATE. REPLACE deletes the old row and inserts a new one — this resets auto-increment IDs, fires DELETE triggers, and breaks foreign keys pointing to the old row.

Why It Matters

The check-then-insert pattern (SELECT to check existence, then INSERT) has a race condition — two concurrent requests can both pass the check and both attempt to insert, causing a duplicate key error. ODKU is atomic.

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

✗ Vulnerable

// 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]);
}

✓ Fixed

-- 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