Introduction

INSERT ... ON DUPLICATE KEY UPDATE lets MySQL treat an insert as an upsert. When you insert multiple rows in one statement, MySQL checks each row against the table's primary key and unique indexes, inserts rows that do not exist yet, and updates rows that hit a duplicate key.

Basic multi-row upsert syntax

Suppose you have a table keyed by sku:

1CREATE TABLE inventory (
2    sku VARCHAR(20) PRIMARY KEY,
3    name VARCHAR(100) NOT NULL,
4    quantity INT NOT NULL,
5    price DECIMAL(10, 2) NOT NULL
6);

Now you can insert or update several rows in one query:

1INSERT INTO inventory (sku, name, quantity, price)
2VALUES
3    ('A100', 'Keyboard', 5, 49.99),
4    ('B200', 'Mouse', 8, 19.99),
5    ('C300', 'Monitor', 2, 199.99) AS new
6ON DUPLICATE KEY UPDATE
7    name = new.name,
8    quantity = quantity + new.quantity,
9    price = new.price;

For each row in the VALUES list:

• If sku does not exist, MySQL inserts it.
• If sku already exists, MySQL runs the UPDATE clause for that row.

That means a single statement can insert brand-new products and restock existing ones at the same time.

Understand what triggers the update

The UPDATE part runs only when the inserted row conflicts with a primary key or unique index. It does not trigger on arbitrary matching columns.

For example, this schema also works:

1CREATE TABLE users (
2    id BIGINT PRIMARY KEY AUTO_INCREMENT,
3    email VARCHAR(255) NOT NULL UNIQUE,
4    login_count INT NOT NULL DEFAULT 0
5);

And this statement increments existing users by unique email:

1INSERT INTO users (email, login_count)
2VALUES
3    ('[email protected]', 1),
4    ('[email protected]', 1) AS new
5ON DUPLICATE KEY UPDATE
6    login_count = login_count + new.login_count;

If there is no primary key or unique index on the column that should define a duplicate, MySQL has nothing to match against and will insert new rows instead of updating old ones.

Use aliases in modern MySQL

Older examples often use VALUES(column_name) inside the update clause:

1INSERT INTO inventory (sku, name, quantity, price)
2VALUES ('A100', 'Keyboard', 5, 49.99)
3ON DUPLICATE KEY UPDATE
4    price = VALUES(price);

That pattern still appears in many blog posts, but row and column aliases are the cleaner modern form for inserted values, especially in newer MySQL 8.0 releases. The alias form also reads better when the update expression is more complex.

For example:

1INSERT INTO inventory (sku, name, quantity, price)
2VALUES
3    ('A100', 'Keyboard', 5, 49.99),
4    ('B200', 'Mouse', 8, 19.99) AS incoming
5ON DUPLICATE KEY UPDATE
6    name = incoming.name,
7    quantity = quantity + incoming.quantity,
8    price = incoming.price;

This makes it obvious which values are from the existing row and which values are from the attempted insert.

Design the update clause carefully

The most important decision is whether duplicate rows should replace existing values or combine with them.

Replace existing value:

price = incoming.price

Add to existing quantity:

quantity = quantity + incoming.quantity

Keep the larger of two values:

quantity = GREATEST(quantity, incoming.quantity)

Your update clause is the actual business rule. The SQL syntax is just the mechanism that applies it row by row.

Why this is better than separate existence checks

A common beginner pattern is:

1. Run SELECT to see whether the row exists.
2. Run INSERT if it does not.
3. Run UPDATE if it does.

That approach is slower and also creates race conditions under concurrency unless you add locking. INSERT ... ON DUPLICATE KEY UPDATE lets MySQL handle the check and write in one statement, which is both simpler and safer.

It is also well suited to batch ingestion jobs because you can send many rows at once instead of issuing one statement per row.

Common Pitfalls

The most common mistake is forgetting the unique constraint. Without a primary key or unique index, there is no duplicate detection.

Another common issue is updating the wrong columns on conflict. For example, blindly overwriting quantity may erase inventory instead of adding to it. The update clause should reflect the business meaning of a duplicate row.

People also copy older examples that use VALUES(column) everywhere without realizing newer MySQL versions support row aliases after the VALUES clause, which is usually clearer for multi-row inserts.

Finally, be careful when the table has multiple unique indexes. A duplicate can be triggered by any of them, which can make the update behavior less obvious than expected.

Summary

• Use INSERT ... VALUES ... AS alias ON DUPLICATE KEY UPDATE ... for multi-row upserts in MySQL.
• Duplicate handling works only through primary keys or unique indexes.
• The update clause runs separately for each conflicting row in the insert set.
• Decide explicitly whether duplicates should overwrite, accumulate, or otherwise transform existing values.
• Prefer a single upsert statement over separate existence checks and per-row updates.