Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 75 ยท Changing Data

Upserts: ON DUPLICATE KEY UPDATE and INSERT IGNORE

Insert a row or update it if it exists: MySQL ON DUPLICATE KEY UPDATE, the row alias replacing VALUES(), INSERT IGNORE hazards, REPLACE, affected rows and deadlocks.

Very often the question is not "insert this row" but "make sure this row exists with these values". A page-view counter should start at 1 the first time and grow afterwards. A synced product should be created if it is new and refreshed if it is already there. Doing this with a SELECT followed by an INSERT or UPDATE is slow and, worse, racy: two requests can both see "not found" and both insert. An upsert does the whole decision in one atomic statement, and the database's unique index is the referee.

MySQL offers INSERT ... ON DUPLICATE KEY UPDATE, the quieter INSERT IGNORE and the blunt REPLACE. This lesson explains each, the rules for the affected-rows number, and the traps that make INSERT IGNORE risky and upserts prone to deadlocks. It builds on INSERT, UPDATE and DELETE in MySQL.

One statement, two outcomes

Key is new The row is inserted.
Key exists The existing row is updated instead.
Atomic No gap between checking and writing.

A PRIMARY KEY or UNIQUE index must define what a duplicate is.

1 / 2

ON DUPLICATE KEY UPDATE

Add the clause to an ordinary INSERT. When the new row would violate a PRIMARY KEY or UNIQUE index, MySQL runs the UPDATE part against the row that is already there:

SQL
CREATE TABLE page_views (
  page VARCHAR(100) PRIMARY KEY,
  hits INT UNSIGNED NOT NULL DEFAULT 0
);

INSERT INTO page_views (page, hits) VALUES ('/home', 1)
ON DUPLICATE KEY UPDATE hits = hits + 1;

The first run inserts ('/home', 1). Every later run finds the key and adds one to hits. In the update part, a bare column name means the existing row's value.

Referring to the new values

Often you want the update to use the values you tried to insert, for instance when syncing a product. Historically that was written with VALUES(column):

SQL
INSERT INTO products (sku, name, price, stock)
VALUES ('PEN-01', 'Pen', 1.80, 50)
ON DUPLICATE KEY UPDATE price = VALUES(price), stock = stock + VALUES(stock);

Since MySQL 8.0.19 you can give the new row an alias and read from it like a table. VALUES() in this position has been deprecated since 8.0.20 and will eventually be removed, so use the alias form in new code:

SQL
INSERT INTO products (sku, name, price, stock)
VALUES ('PEN-01', 'Pen', 1.80, 50) AS new
ON DUPLICATE KEY UPDATE price = new.price, stock = products.stock + new.stock;

Here new.price is the value you offered, and products.stock is the stored value, which makes the intent unmistakable. You can also alias the columns (AS new(s, n, p, q)). The alias works with the VALUES and SET forms. For INSERT ... SELECT, read the incoming values through the source query's own aliases.

Rules and surprises

  • Affected rows is 1, 2 or 0. 1 means a new row was inserted. 2 means an existing row was updated. 0 means the row existed and nothing needed to change (the statement did not alter any value). Client libraries can request "found rows" semantics instead, which turns the 0 into 1, so check your driver's flag before you build logic on this number.
  • Only one row is updated. If the new row collides with more than one existing row through different unique indexes, MySQL updates just one of them, in an unpredictable choice. Keep upsert tables to one unique key, or know exactly which key will conflict.
  • AUTO_INCREMENT gaps. The insert is attempted first and usually consumes an id, even when the statement ends up updating. Frequent upserts on a table with an auto-increment key burn through numbers. Use BIGINT and never treat ids as gapless (AUTO_INCREMENT, UUIDs and ID Strategies).
  • Getting the id. After an upsert, LAST_INSERT_ID() only reflects a new row. To also get the id of an updated row, write ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id), ....
  • Column order in UPDATE matters because assignments run left to right and see earlier changes, as covered in the previous lesson.
  • Batches work. One statement can carry hundreds of rows, each upserted independently, which is much faster than a loop of single statements.
  • No MERGE. MySQL does not implement the standard MERGE statement. The upsert syntax above is the way.

The standard equivalent in SQLite is ON CONFLICT (...) DO UPDATE, where the incoming row is called excluded. The idea is the same, so the following example is a faithful stand-in:

SQL A counter and a price sync with upserts

Press Run (or Ctrl + Enter) to see the result here.

The counter reaches 3 for /home and 1 for /about. In the second part the pen is refreshed (price 1.80, stock 150), the mug is inserted, and the notebook is untouched. Note one difference from MySQL: SQLite's changes() would report 1 in both cases, while MySQL reports 1 for an insert and 2 for an update.

Conditional updates

Sometimes the stored row should win unless the new one is newer. In MySQL you can express this with IF inside the update, and since assignments run in order, put the column that decides last:

SQL
INSERT INTO sensor_latest (sensor_id, reading, read_at)
VALUES (7, 21.4, '2026-03-07 10:05:00') AS new
ON DUPLICATE KEY UPDATE
  reading = IF(new.read_at > read_at, new.reading, reading),
  read_at = GREATEST(read_at, new.read_at);

The reading assignment runs first and compares with the old read_at. Only then is read_at updated. Swap the lines and the comparison would see the new timestamp, and the reading would never be replaced. SQLite lets you add a WHERE to the conflict clause instead, which is often cleaner:

SQL Keep the newest reading only

Press Run (or Ctrl + Enter) to see the result here.

The row ends with 21.4 at 10:05. The late reading from 09:50 found the key, failed the WHERE, and changed nothing.

INSERT IGNORE

INSERT IGNORE turns certain errors into warnings, so the statement carries on:

SQL
INSERT IGNORE INTO subscribers (email) VALUES ('[email protected]'), ('[email protected]');

If [email protected] already exists, that row is skipped and the rest is inserted. This looks handy for "add these if they are missing", but the keyword ignores far more than duplicates:

  • Invalid data is coerced instead of rejected. A string too long for the column is truncated, 'abc' inserted into an INT becomes 0, a NULL in a NOT NULL column becomes the type's implicit default, and an out-of-range number is clamped to the limit. Each case only produces a warning.
  • Foreign key violations are skipped rather than reported as errors, so rows may vanish with no sign.
  • The auto-increment counter still advances for the skipped rows.
  • The number of affected rows is the number actually inserted, and you must call SHOW WARNINGS to learn why the others were dropped.

If you only want to skip duplicates, say so precisely. A no-op update does it without the side effects:

SQL
INSERT INTO subscribers (email) VALUES ('[email protected]')
ON DUPLICATE KEY UPDATE email = email;

Real errors such as an oversized value still raise in strict mode, and only the duplicate-key case is absorbed. SQLite shows the same hazard under a different name: INSERT OR IGNORE also skips rows that violate NOT NULL, while ON CONFLICT DO NOTHING ignores only uniqueness conflicts and still reports other errors, which is the behavior you usually want:

SQL OR IGNORE hides more than duplicates

Press Run (or Ctrl + Enter) to see the result here.

Only Ana (still on free, not upgraded) and Ben are stored. Cara disappeared without an error, which is exactly the kind of silent loss that makes INSERT IGNORE dangerous. rows_inserted is 1.

REPLACE compared

REPLACE INTO deletes the colliding row and inserts the new one. Compared with ON DUPLICATE KEY UPDATE:

ON DUPLICATE KEY UPDATE INSERT IGNORE REPLACE
On a duplicate key updates the existing row skips the new row deletes the old row, inserts the new one
Columns not mentioned keep their values not applicable reset to defaults
Delete triggers and ON DELETE CASCADE not fired not fired fire
Other errors still raised hidden as warnings still raised
Affected rows 1 / 2 / 0 1 or 0 1 or 2

In almost every case ON DUPLICATE KEY UPDATE is the right choice. REPLACE is justified only when you really want a fresh row and have checked that no foreign key, trigger or unlisted column will suffer.

Concurrency and deadlocks

An upsert is atomic for one row, but busy systems can still hit deadlocks (error 1213), because InnoDB takes locks on the index entries it touches, including gap locks when a key does not exist yet (Locking, Gap Locks and Deadlocks). Two sessions upserting overlapping sets of keys in different orders, or several inserting into the same gap, can each wait for the other. To keep this under control:

  • Sort the rows of a batch by the unique key so all sessions lock in the same order.
  • Keep transactions short and commit right after the upsert.
  • Retry the transaction when you receive a deadlock error. MySQL rolls back the victim, so a retry is the standard answer.
  • Avoid upserts on tables with several unique indexes in hot paths, or accept that one conflict may block another.
  • Where the order of arrival does not matter, aggregate in the application first and write one row per key.

What ran live here

The upserts, conditional update and ignore examples ran in SQLite with ON CONFLICT, excluded and INSERT OR IGNORE, which behave like their MySQL cousins but use different syntax and report affected rows differently. ON DUPLICATE KEY UPDATE, the row alias, VALUES(), INSERT IGNORE, REPLACE, LAST_INSERT_ID(id) and deadlock behavior are MySQL features shown as read-only code.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does an affected-rows value of 2 mean after INSERT ... ON DUPLICATE KEY UPDATE?

    MySQL reports 1 for an inserted row, 2 for an existing row that was updated, and 0 when the row existed but no value actually changed.
  2. 2. Which spelling should new code use to read the incoming value in the UPDATE part?

    VALUES() in this position has been deprecated since MySQL 8.0.20. The row alias from 8.0.19 is the replacement. EXCLUDED is SQLite and PostgreSQL syntax.
  3. 3. Why is INSERT IGNORE risky?

    Besides skipping duplicates, it truncates or coerces invalid values and skips some rows silently, so data problems can go unnoticed.
  4. 4. What does REPLACE do that ON DUPLICATE KEY UPDATE does not?

    REPLACE is a delete plus an insert. Unlisted columns reset to defaults, and delete triggers and ON DELETE CASCADE actions run.
  5. 5. What is a good first response to a deadlock error (1213) on an upsert?

    InnoDB rolls back one transaction to break the cycle. Retrying it is the normal handling, and consistent lock ordering makes deadlocks rarer.

Summary

  • An upsert decides between insert and update in one atomic statement, using a PRIMARY KEY or UNIQUE index to detect the duplicate.
  • INSERT ... ON DUPLICATE KEY UPDATE is the default choice. Use a row alias (AS new, MySQL 8.0.19+) instead of the deprecated VALUES(), and remember that assignments run left to right.
  • Affected rows is 1 (inserted), 2 (updated) or 0 (unchanged). Upserts can burn AUTO_INCREMENT values and only update one row if several keys conflict.
  • INSERT IGNORE hides invalid data and other errors along with duplicates. A no-op ON DUPLICATE KEY UPDATE col = col skips duplicates safely.
  • REPLACE deletes and re-inserts, resetting columns and firing cascades.
  • Keep upserts short, sort batch keys and retry on deadlocks.

Next: Bulk Loading and Exporting Data.