How MySQL ON DUPLICATE KEY UPDATE Works

This article explains how MySQL processes the ON DUPLICATE KEY UPDATE clause during an INSERT operation. It covers the mechanics of how MySQL detects duplicate keys, how it decides whether to insert a new row or update an existing one, and the impact of this clause on affected row counts and auto-increment values.

The Core Mechanics of ON DUPLICATE KEY UPDATE

When you execute an INSERT statement containing the ON DUPLICATE KEY UPDATE clause, MySQL attempts to insert the new row into the database table first.

If the insert operation succeeds without violating any unique constraints, MySQL inserts the row, and the operation is complete. However, if the insert fails because a value violates a PRIMARY KEY or a UNIQUE index constraint, MySQL catches this error internally and immediately performs an UPDATE on the existing row using the column-value pairs specified after the UPDATE keyword.

Referencing Attempted Values

To update the existing row with the values you originally tried to insert, MySQL provides a way to reference those pending values.

In older versions of MySQL, you use the VALUES(col_name) function:

INSERT INTO users (id, username, email) 
VALUES (1, 'john_doe', 'john@example.com')
ON DUPLICATE KEY UPDATE email = VALUES(email);

Starting with MySQL 8.0.20, the VALUES() function is deprecated. Instead, you should use row aliases to reference the new values:

INSERT INTO users (id, username, email) 
VALUES (1, 'john_doe', 'john@example.com') AS new_data
ON DUPLICATE KEY UPDATE email = new_data.email;

How Affected Rows are Calculated

The database engine returns different “affected rows” counts depending on the action taken during the execution of the statement: * 1 if the row is inserted as a brand-new record. * 2 if an existing row is updated. * 0 if an existing row is matched, but the update does not change any values (the database detects that the new values are identical to the old ones and skips writing to disk).

Impact on Auto-Increment Columns

If the table contains an AUTO_INCREMENT column, running an INSERT ... ON DUPLICATE KEY UPDATE statement will still increment the internal auto-increment counter, even if the operation results in an UPDATE rather than an INSERT. This behavior, governed by the innodb_autoinc_lock_mode configuration, can result in gaps in your primary key sequence over time.

Multi-Column Unique Keys Warning

If a table has multiple UNIQUE indexes, and the insert attempt conflicts with more than one unique constraint across different rows, MySQL will only update one of the conflicting rows. Because the order of evaluation depends on the physical storage and index structures, the specific row that gets updated is not guaranteed. For predictable results, it is best to use this clause on tables that have only one unique key constraint.