MySQL ON DUPLICATE KEY UPDATE: Insert or Update (Upsert)

An upsert means “insert this row, but if it already exists, update it instead”. MySQL does it with INSERT ... ON DUPLICATE KEY UPDATE.

query.sql

SQL

CREATE TABLE page_views (
    page VARCHAR(50) PRIMARY KEY,
    views INT NOT NULL DEFAULT 0,
    last_seen DATE NOT NULL
);

query.sql

SQL

1
2

INSERT INTO page_views (page, views, last_seen) VALUES ('/home', 1, '2026-07-01')
ON DUPLICATE KEY UPDATE views = views + 1, last_seen = '2026-07-01';

Output

OUTPUT

Query OK, 1 row affected

The row was new, so it was inserted (1 row affected). Run a similar statement again:

query.sql

SQL

1
2
3

INSERT INTO page_views (page, views, last_seen) VALUES ('/home', 1, '2026-07-02')
ON DUPLICATE KEY UPDATE views = views + 1, last_seen = '2026-07-02';
SELECT * FROM page_views;

Output

OUTPUT

Query OK, 2 rows affected

+-------+-------+------------+
| page  | views | last_seen  |
+-------+-------+------------+
| /home |     2 | 2026-07-02 |
+-------+-------+------------+
1 row in set

Now /home already existed (the PRIMARY KEY matched), so MySQL ran the UPDATE part. The count went from 1 to 2.

Rows affected

Outcome	`ROW_COUNT()` / “rows affected”
New row inserted	1
Existing row updated	2
Existing row found but values did not change	0

The “2” for an update surprises many people. It is how MySQL signals “insert did not happen, update did”. Do not use the count as the number of rows touched.

Using the new values: the row alias

Often you want “set the column to the value I tried to insert”. The modern way (MySQL 8.0.19 and later) uses a row alias:

query.sql

SQL

CREATE TABLE prices (
    sku VARCHAR(20) PRIMARY KEY,
    price DECIMAL(8,2) NOT NULL,
    updated_on DATE NOT NULL
);
INSERT INTO prices VALUES ('NB-01', 80.00, '2026-01-01');

INSERT INTO prices (sku, price, updated_on) VALUES ('NB-01', 85.00, '2026-07-01'), ('PEN-01', 15.00, '2026-07-01') AS new
ON DUPLICATE KEY UPDATE price = new.price, updated_on = new.updated_on;
SELECT * FROM prices ORDER BY sku;

Output

OUTPUT

+--------+-------+------------+
| sku    | price | updated_on |
+--------+-------+------------+
| NB-01  | 85.00 | 2026-07-01 |
| PEN-01 | 15.00 | 2026-07-01 |
+--------+-------+------------+
2 rows in set

NB-01 was updated to 85, and PEN-01 was inserted. AS new gives a name to the row you tried to insert, and new.price reads its value.

info

The old VALUES() function

Older code writes price = VALUES(price). It still works but is deprecated since MySQL 8.0.20 and gives a warning. Use the alias form.

Which key triggers it?

The update runs when the new row would break any PRIMARY KEY or UNIQUE index. If a table has several unique indexes, one new row may match different old rows, and MySQL updates only the first one. Keep upserts on tables with one unique key, or think hard about the case.

A safe counter

Because the statement is one atomic operation, it is a safe way to count. Two connections adding at the same time do not lose an update:

query.sql

SQL

INSERT INTO page_views (page, views, last_seen) VALUES ('/pricing', 1, '2026-07-03')
ON DUPLICATE KEY UPDATE views = views + 1;
INSERT INTO page_views (page, views, last_seen) VALUES ('/pricing', 1, '2026-07-03')
ON DUPLICATE KEY UPDATE views = views + 1;
SELECT page, views FROM page_views ORDER BY page;

Output

OUTPUT

+----------+-------+
| page     | views |
+----------+-------+
| /home    |     2 |
| /pricing |     2 |
+----------+-------+
2 rows in set

AUTO_INCREMENT side effect

An upsert on a table with an AUTO_INCREMENT column burns an ID each time the update branch runs, because MySQL reserves the number before it knows about the duplicate. Frequent upserts can use up the numbers (or leave gaps) faster than you expect. Another reason to prefer BIGINT UNSIGNED on busy upsert tables.

lightbulb

Standard SQL

The SQL standard way is MERGE, which MySQL does not have. INSERT ... ON DUPLICATE KEY UPDATE is MySQL-specific. PostgreSQL uses ON CONFLICT.