Upserts: ON DUPLICATE KEY UPDATE, REPLACE and INSERT IGNORE

An upsert inserts a row, or updates it if it already exists. In MySQL, "already exists" always means: the new row would violate a PRIMARY KEY or UNIQUE index. That makes the unique key the real business rule. If "one counter per page" is not enforced by a unique index on page, no upsert will enforce it for you.

Why not SELECT first and then choose between INSERT and UPDATE? Because two sessions can run that SELECT at the same moment, both see no row, and both insert: without a unique key you get two rows, with one the second session fails with a duplicate-key error. A single upsert statement leaves the decision to the unique index, inside one statement, so there is no gap between the check and the write for another session to use.

INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO tags (name, hits) VALUES ('mysql', 1) AS new
ON DUPLICATE KEY UPDATE hits = tags.hits + new.hits;

The row alias (AS new, MySQL 8.0.19 and later) names the row you tried to insert; tags.hits is the value already stored. The older spelling VALUES(hits) still works but is deprecated: the example's third statement uses it and gets warning 1287.

1CREATE TABLE tags (2  id   INT AUTO_INCREMENT PRIMARY KEY,3  name VARCHAR(20) NOT NULL,4  hits INT NOT NULL,5  UNIQUE KEY uq_name (name)6);

1INSERT INTO tags (name, hits) VALUES ('mysql', 1) AS new2ON DUPLICATE KEY UPDATE hits = tags.hits + new.hits;3SET @first = ROW_COUNT();4 5INSERT INTO tags (name, hits) VALUES ('mysql', 1) AS new6ON DUPLICATE KEY UPDATE hits = tags.hits + new.hits;7SET @second = ROW_COUNT();8 9-- The deprecated VALUES() spelling: it works, with a warning.10INSERT INTO tags (name, hits) VALUES ('sql', 1)11ON DUPLICATE KEY UPDATE hits = hits + VALUES(hits);12GET DIAGNOSTICS CONDITION 1 @old_spelling_warning = MYSQL_ERRNO;13 14-- An update that leaves the row exactly as it was.15INSERT INTO tags (name, hits) VALUES ('mysql', 1) AS new16ON DUPLICATE KEY UPDATE hits = tags.hits;17SET @unchanged = ROW_COUNT();18 19SELECT @first  AS affected_on_insert,20       @second AS affected_on_update,21       @unchanged AS affected_unchanged,22       @old_spelling_warning AS values_fn_warning,23       GROUP_CONCAT(CONCAT(id, ':', name, ':', hits) ORDER BY id) AS id_name_hits24FROM tags;

Things to notice:

• Affected rows is 1 when a row was inserted and 2 when an existing row was updated. When the update leaves the row exactly as it was, the server reports 0, or 1 if the client connected with the CLIENT_FOUND_ROWS flag. This sandbox's driver sets that flag, which is why the last upsert reports 1; check what your own driver does before branching on the number.
• The auto-increment counter moves anyway. InnoDB reserves an id before it knows the insert will become an update, so the second tag got id 3, not 2. Gaps in an AUTO_INCREMENT column are normal and must never be relied on to count rows.

The update runs the same way for an INSERT ... SELECT, so a batch with the same key twice adds both quantities, which the exercise relies on.

REPLACE is delete-then-insert

REPLACE INTO looks like an upsert and is not. When the new row collides, MySQL deletes the old row and inserts the new one.

1CREATE TABLE users (2  id      INT PRIMARY KEY,3  email   VARCHAR(50) NOT NULL UNIQUE,4  name    VARCHAR(20),5  created DATE NOT NULL DEFAULT '2026-01-01'6);7CREATE TABLE orders (8  id      INT PRIMARY KEY,9  user_id INT,10  FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE11);12INSERT INTO users VALUES (1, 'ann@x.io', 'Ann', '2025-05-05');13INSERT INTO orders VALUES (100, 1), (101, 1);

1REPLACE INTO users (id, email, name) VALUES (1, 'ann@x.io', 'Ann B');2SET @affected = ROW_COUNT();3 4SELECT @affected AS affected,5       (SELECT CONCAT(name, ' / ', created) FROM users WHERE id = 1) AS user_now,6       (SELECT COUNT(*) FROM orders) AS orders_left;

Affected rows is 2 (one delete, one insert). Every column you did not supply was reset to its default, so the original created date is gone. And because it was a real delete, ON DELETE CASCADE removed both of the user's orders. Use ON DUPLICATE KEY UPDATE unless you truly mean "throw the old row away".

INSERT IGNORE ignores more than duplicates

INSERT IGNORE skips rows that would duplicate a key, which is sometimes what you want. It also downgrades other errors to warnings, including the strict-mode errors from the data-types lesson.

1CREATE TABLE codes (id INT PRIMARY KEY, code VARCHAR(3) NOT NULL UNIQUE, n TINYINT);2INSERT INTO codes VALUES (1, 'abc', 1);

1INSERT IGNORE INTO codes VALUES2  (1, 'xyz', 5),        -- duplicate id3  (2, 'abc', 6),        -- duplicate code4  (3, 'toolong', 7),    -- too long for VARCHAR(3)5  (4, 'ok', 300);       -- out of range for TINYINT6SET @warnings = @@warning_count;7 8SELECT @warnings AS warnings, id, code, n9FROM codes10ORDER BY id;

Two rows were skipped as duplicates, but 'toolong' was stored as 'too' and 300 as 127, with four warnings and no error. If you only mean "skip duplicates", ON DUPLICATE KEY UPDATE id = id does that and still fails on bad data.

Common mistakes

• Upserting without a unique index on the natural key. Every attempt inserts a new row.
• Using REPLACE as an upsert. It resets unspecified columns and fires delete cascades, as the orders count of 0 shows.
• Using INSERT IGNORE to skip duplicates. It also accepts truncated and clamped values silently.
• Expecting AUTO_INCREMENT to be gap-free. The upsert consumed id 2.

What interviewers ask

• How do you upsert in MySQL? INSERT ... ON DUPLICATE KEY UPDATE, keyed on a PRIMARY KEY or UNIQUE index, using a row alias for the new values.
• What is the difference between REPLACE and ON DUPLICATE KEY UPDATE? REPLACE deletes and re-inserts (defaults reset, cascades fire); the other updates the existing row in place.