UPSERT
Insert-or-update in MySQL: ON DUPLICATE KEY UPDATE with row aliases, INSERT IGNORE, REPLACE, and which one is actually idempotent.
INSERT INTO tbl_name (col, ...) VALUES (value_list) AS new_row [(col_alias, ...)] ON DUPLICATE KEY UPDATE col = new_row.col, ...An upsert writes a row if it is absent and updates it if it is present, so the same statement can be run twice without creating a duplicate. MySQL 8.4 offers three mechanisms, and they are not interchangeable.
ON DUPLICATE KEY UPDATE
This is the one you want. When the new row would collide on a PRIMARY KEY or UNIQUE index, MySQL updates the existing row instead of erroring. The affected-rows value is 1 for an insert, 2 for an update, and 0 when the existing row was already at those values.
Shorthand and longhand for the incoming row
To reference the values you tried to insert, name the row with an alias: ... VALUES (1,2,3) AS new ON DUPLICATE KEY UPDATE c = new.a + new.b. You can alias the columns too: AS new(m, n, p), then write c = m + n. The older spelling used the VALUES() function — c = VALUES(a) + VALUES(b) — which is deprecated as of MySQL 8.0.20 and subject to removal. They mean the same thing; you will meet both in existing code.
What trips people up
INSERT IGNORE is not an upsert: it discards the new row and leaves the old one untouched, and it also silences conversion and truncation errors. REPLACE is not an upsert either — it deletes the old row and inserts a new one, so unspecified columns fall back to their defaults, ON DELETE CASCADE children disappear, and a new AUTO_INCREMENT id is issued. Avoid ON DUPLICATE KEY UPDATE on a table with several unique indexes: only one row is updated and which one is not defined.
Example
-- Idempotent daily rollup. Re-running it for the same day overwrites
-- rather than duplicating, because (day_key, metric) is a UNIQUE key.
-- CREATE TABLE metric_daily (
-- id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
-- day_key DATE NOT NULL,
-- metric VARCHAR(64) NOT NULL,
-- total BIGINT NOT NULL DEFAULT 0,
-- runs INT NOT NULL DEFAULT 1,
-- UNIQUE KEY uq_day_metric (day_key, metric)
-- ) ENGINE=InnoDB;
INSERT INTO metric_daily (day_key, metric, total)
VALUES ('2026-09-17', 'signups', 412),
('2026-09-17', 'logins', 9310)
AS new -- row alias, MySQL 8.0.19 and later
ON DUPLICATE KEY UPDATE
total = new.total, -- new.* = the row we tried to insert
runs = metric_daily.runs + 1; -- unqualified/table-qualified = the stored row
-- 1 per inserted row, 2 per updated row, 0 per row already at these values.
SELECT ROW_COUNT();When to use it
- Re-run a nightly aggregation job without duplicating yesterday's rows.
- Record a counter or a last-seen timestamp that must increment on repeat writes.
- Sync rows from an external system where you cannot tell in advance which already exist.
- Make a queue consumer safe to retry after a timeout it did not actually lose.
More examples
The deprecated VALUES() spelling and its replacement
Worth reading closely, because the two halves of the assignment mean different rows: `inventory.on_hand` is what is stored, `new.on_hand` is what arrived. Getting them backwards turns an accumulating counter into an overwriting one. Migrate VALUES() to a row alias when you touch the code — it is deprecated, not broken, so there is no rush, but new statements should use the alias.
-- Longhand you will still find in older migrations and ORM output.
-- The VALUES() function is DEPRECATED as of MySQL 8.0.20 and may be removed.
INSERT INTO inventory (sku, on_hand, last_seen)
VALUES ('KB-87', 12, NOW())
ON DUPLICATE KEY UPDATE
on_hand = inventory.on_hand + VALUES(on_hand),
last_seen = VALUES(last_seen);
-- The current spelling. Identical behaviour, using a row alias.
INSERT INTO inventory (sku, on_hand, last_seen)
VALUES ('KB-87', 12, NOW())
AS new
ON DUPLICATE KEY UPDATE
on_hand = inventory.on_hand + new.on_hand,
last_seen = new.last_seen;
-- Column aliases are allowed too; the row alias is still required.
INSERT INTO inventory (sku, on_hand, last_seen)
VALUES ('KB-87', 12, NOW())
AS new(n_sku, n_on_hand, n_last_seen)
ON DUPLICATE KEY UPDATE
on_hand = inventory.on_hand + n_on_hand,
last_seen = n_last_seen;INSERT IGNORE and REPLACE: what they actually do
Show this to anyone who reaches for REPLACE as a convenient upsert. Because it is a real delete, any child rows attached by a FOREIGN KEY with ON DELETE CASCADE go with it, unlisted columns silently reset to their defaults, and the row gets a fresh AUTO_INCREMENT id that breaks anything holding the old one. REPLACE earns its place only when you genuinely want the whole row replaced, and it requires both INSERT and DELETE privileges.
-- INSERT IGNORE: on a duplicate key the NEW row is thrown away and the old one
-- is left exactly as it was. Useful only for "insert if absent, otherwise do nothing".
-- It also demotes data-conversion and truncation errors to warnings, so it can
-- hide a bad column value as easily as a duplicate.
INSERT IGNORE INTO tags (name) VALUES ('mysql');
SHOW WARNINGS; -- always look; IGNORE is silent by design
-- REPLACE: DELETE the conflicting row, then INSERT the new one.
-- Columns not listed get their DEFAULT, not their previous value:
-- created_at below is overwritten even though it was never mentioned.
REPLACE INTO tags (id, name) VALUES (9, 'mysql');
-- Affected rows: 1 means inserted only; greater than 1 means a row was deleted first.Upserting from a query
This is the shape of a nightly aggregate job that can be re-run safely after a failure. Two constraints to remember: the AS row-alias syntax belongs to the VALUES form, so the SELECT form still uses VALUES(); and a top-level UNION in the SELECT is not supported, which is why the derived-table rewrite is in the comment. Under MIXED binary logging these statements are written row-based, since their row order is not guaranteed.
-- INSERT ... SELECT combines with ON DUPLICATE KEY UPDATE, which makes
-- a whole recomputed slice idempotent in one statement.
INSERT INTO customer_totals (customer_id, order_count, lifetime_cents)
SELECT o.customer_id,
COUNT(*),
SUM(o.total_cents)
FROM orders AS o
WHERE o.status = 'shipped'
GROUP BY o.customer_id
ON DUPLICATE KEY UPDATE
order_count = VALUES(order_count), -- row aliases are not available
lifetime_cents = VALUES(lifetime_cents); -- on the INSERT ... SELECT form
-- A UNION in the SELECT is rejected here; wrap it as a derived table first:
-- INSERT INTO t1 (a, b)
-- SELECT * FROM (SELECT c, d FROM t2 UNION SELECT e, f FROM t3) AS dt
-- ON DUPLICATE KEY UPDATE b = b + c;
Discussion