Upserts: ON DUPLICATE KEY, IGNORE and REPLACE

When the row might already exist

A normal INSERT fails if it would create a duplicate primary key or unique value. Often that is what you want. But sometimes the question is "make sure this row exists with these values", for example syncing product data from a supplier feed, counting page views, or saving user settings. Checking first with a SELECT and then inserting or updating is slow, and two processes can race each other. MySQL gives you single-statement answers.

INSERT IGNORE: skip duplicates

INSERT IGNORE INTO categories (id, name) VALUES (1, 'Gadgets'), (6, 'Garden');

SELECT * FROM categories;

+----+-------------+
| id | name        |
+----+-------------+
|  1 | Electronics |
|  2 | Books       |
|  3 | Home        |
|  4 | Sports      |
|  5 | Toys        |
|  6 | Garden      |
+----+-------------+

Category 1 already existed, so that row was skipped and its name is still Electronics. The new category 6 was inserted. The statement produced a warning, not an error, for the duplicate:

SHOW WARNINGS;

The catch: IGNORE turns many other errors into warnings too, such as text being cut to fit a column. Use it only when skipping duplicates is exactly what you want, and prefer an explicit ON DUPLICATE KEY UPDATE otherwise.

ON DUPLICATE KEY UPDATE: the upsert

This clause says "if the insert would collide with a primary or unique key, update the existing row instead". Here a supplier delivers 40 more units of product 1, and a new product 16:

INSERT INTO products (id, name, category_id, price, stock)
VALUES (1, 'Wireless Mouse', 1, 24.99, 40), (16, 'Travel Adapter', 1, 15.00, 100)
AS new
ON DUPLICATE KEY UPDATE stock = products.stock + new.stock;
SELECT ROW_COUNT() AS reported;

+----------+
| reported |
+----------+
|        3 |
+----------+

SELECT id, name, stock FROM products WHERE id IN (1, 16);

+----+----------------+-------+
| id | name           | stock |
+----+----------------+-------+
|  1 | Wireless Mouse |   190 |
| 16 | Travel Adapter |   100 |
+----+----------------+-------+

Product 1 existed, so its stock went from 150 to 190. Product 16 did not, so it was inserted with stock 100.

The AS new part gives the incoming row a name so you can refer to its values as new.stock. This alias form works from MySQL 8.0.19. Older code uses the function VALUES(stock) instead, which still works but is deprecated.

The ROW_COUNT() call right after the statement reports how many rows were affected. It counts 1 for each inserted row, 2 for each row changed by the update part, and 0 for an existing row whose values did not change. Here that adds up to 3: two for product 1 and one for product 16. Call it straight after the statement, because any other statement, even a SELECT, resets it.

Note that the table needs a unique key for this to be meaningful. If id were not a primary key or unique, there would never be a duplicate and every statement would insert.

A counter example

Upserts are perfect for counters. Create a small table and count visits:

CREATE TABLE page_views (page VARCHAR(50) PRIMARY KEY, views INT NOT NULL);
INSERT INTO page_views (page, views) VALUES ('home', 1) AS new ON DUPLICATE KEY UPDATE views = page_views.views + 1;
INSERT INTO page_views (page, views) VALUES ('home', 1) AS new ON DUPLICATE KEY UPDATE views = page_views.views + 1;
INSERT INTO page_views (page, views) VALUES ('about', 1) AS new ON DUPLICATE KEY UPDATE views = page_views.views + 1;
SELECT * FROM page_views ORDER BY page;

+-------+-------+
| page  | views |
+-------+-------+
| about |     1 |
| home  |     2 |
+-------+-------+

The same statement works whether or not the row exists yet, and increments atomically, so many visitors at once cannot lose a count.

REPLACE: delete and insert

REPLACE works like INSERT, but if the key exists it first deletes the old row, then inserts the new one. It looks convenient, but it has traps. Columns you do not mention go back to their defaults instead of keeping their old values. Here Paula's manager and department are lost, because the REPLACE did not mention them:

SELECT id, name, manager_id, department_id FROM employees WHERE id = 5;
REPLACE INTO employees (id, name, job_title, salary, hire_date)
VALUES (5, 'Paula Gomez', 'Developer', 85000.00, '2021-04-12');
SELECT id, name, manager_id, department_id FROM employees WHERE id = 5;

+----+-------------+------------+---------------+
| id | name        | manager_id | department_id |
+----+-------------+------------+---------------+
|  5 | Paula Gomez |          3 |             1 |
+----+-------------+------------+---------------+
+----+-------------+------------+---------------+
| id | name        | manager_id | department_id |
+----+-------------+------------+---------------+
|  5 | Paula Gomez |       NULL |          NULL |
+----+-------------+------------+---------------+

The delete half also runs into foreign keys. Every category has products pointing at it, so MySQL refuses to replace one:

REPLACE INTO categories (id, name) VALUES (5, 'Toys and Games');

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`shop`.`products`, CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`))

REPLACE can also fire delete triggers and always uses up a new AUTO_INCREMENT value. For these reasons, INSERT ... ON DUPLICATE KEY UPDATE is the safer habit: it changes only the columns you name.

Which one to use

Common mistakes

• Using INSERT IGNORE everywhere and hiding real data problems.
• Using REPLACE for partial updates, which resets omitted columns.
• Forgetting that a unique key is required for an upsert to detect duplicates.
• Updating the key itself inside ON DUPLICATE KEY UPDATE.
• Mixing up old and new values in the update expression. products.stock is the existing row; new.stock is the incoming one.

Exercise

A feed delivers product 2 (Mechanical Keyboard) with a new price of 84.00, and a new product 17, Laptop Stand, category 1, price 35.00, stock 60. Write one upsert statement that updates the price of the existing product only and inserts the new one.

INSERT INTO products (id, name, category_id, price, stock)
VALUES (2, 'Mechanical Keyboard', 1, 84.00, 60), (17, 'Laptop Stand', 1, 35.00, 60)
AS new
ON DUPLICATE KEY UPDATE price = new.price;
SELECT id, name, price, stock FROM products WHERE id IN (2, 17);

+----+---------------------+-------+-------+
| id | name                | price | stock |
+----+---------------------+-------+-------+
|  2 | Mechanical Keyboard | 84.00 |    60 |
| 17 | Laptop Stand        | 35.00 |    60 |
+----+---------------------+-------+-------+