Menu

MySQL UPSERT with ON DUPLICATE KEY UPDATE

MySQL does not use a separate UPSERT statement. The usual insert-or-update pattern is INSERT ... ON DUPLICATE KEY UPDATE: MySQL inserts a new row unless the insert conflicts with a PRIMARY KEY or UNIQUE index, in which case it updates the existing row.

Basic UPSERT example

Create a table whose SKU is unique:

CREATE TABLE inventory (
  sku VARCHAR(30) NOT NULL PRIMARY KEY,
  product_name VARCHAR(100) NOT NULL,
  quantity INT NOT NULL DEFAULT 0
);

In MySQL 8.0.19 and later, give the proposed row an alias after the VALUES list. Use that alias to refer to incoming values in the update clause:

INSERT INTO inventory (sku, product_name, quantity)
VALUES ('BK-15', 'Book', 5) AS incoming
ON DUPLICATE KEY UPDATE
  product_name = incoming.product_name,
  quantity = inventory.quantity + incoming.quantity;

The first execution inserts BK-15 with a quantity of 5. If you run the statement again with the same SKU and a quantity of 3, MySQL finds the duplicate primary key, updates the product name, and adds 3 to the existing quantity, making it 8.

The alias refers to the row that would have been inserted. inventory.quantity refers to the value already stored in the conflicting row. To replace the old quantity instead of adding to it, use quantity = incoming.quantity.

MySQL documents this row-alias syntax for ON DUPLICATE KEY UPDATE in the INSERT reference. For other insert forms, see MySQL INSERT and insert multiple rows.

Insert or update several rows

You can use a row alias after a multi-row VALUES list as well:

INSERT INTO inventory (sku, product_name, quantity)
VALUES
  ('BK-15', 'Book', 5),
  ('PN-02', 'Pen', 4)
AS incoming
ON DUPLICATE KEY UPDATE
  product_name = incoming.product_name,
  quantity = inventory.quantity + incoming.quantity;

Each row is inserted when its SKU is new; a row with an existing SKU follows the update expressions.

Upsert rows selected from another table

When the incoming rows come from a query, use INSERT ... SELECT. The row alias after a VALUES list does not apply to this form. Expose the incoming columns through a derived table, then refer to those output aliases in the update clause:

INSERT INTO inventory (sku, product_name, quantity)
SELECT sku, incoming_name, incoming_quantity
FROM (
  SELECT
    sku,
    product_name AS incoming_name,
    quantity AS incoming_quantity
  FROM incoming_inventory
) AS source_rows
ON DUPLICATE KEY UPDATE
  product_name = incoming_name,
  quantity = inventory.quantity + incoming_quantity;

This form avoids using the deprecated VALUES(column) function in an INSERT ... SELECT upsert. MySQL documents the derived-table approach and its restrictions in the INSERT ... ON DUPLICATE KEY UPDATE reference. For more INSERT ... SELECT examples, see MySQL INSERT INTO SELECT.

MySQL 5.7 and the older VALUES(column) form

MySQL 5.7 does not support row aliases in this clause. Older code commonly refers to the proposed value with VALUES(column):

INSERT INTO inventory (sku, product_name, quantity)
VALUES ('BK-15', 'Book', 5)
ON DUPLICATE KEY UPDATE
  product_name = VALUES(product_name),
  quantity = quantity + VALUES(quantity);

This form still works in older releases, but MySQL deprecated VALUES() for referring to the new row in ON DUPLICATE KEY UPDATE in MySQL 8.0.20. Use the row-alias syntax for MySQL 8.0.19 and later. The MySQL 8.0.20 release notes describe the deprecation and replacement.

Which key triggers the update?

MySQL takes this path when the inserted values duplicate a PRIMARY KEY or any UNIQUE index. The statement does not name a specific conflict target as PostgreSQL does. This makes the table’s unique constraints part of the upsert behavior.

Be careful when a table has multiple unique indexes: a proposed row could conflict with different existing rows through different keys. MySQL documents that only one matching row is updated and recommends avoiding this design with ON DUPLICATE KEY UPDATE when possible. See the multiple-unique-index caveat.

UPSERT or INSERT IGNORE?

INSERT ... ON DUPLICATE KEY UPDATE changes the existing row when a unique key conflicts. INSERT IGNORE skips rows with ignorable errors, including duplicate keys, and reports warnings instead. Use the form that matches whether you want to update or skip the conflicting row.

Summary

  • Add a PRIMARY KEY or UNIQUE index to define what counts as a duplicate.
  • Use ON DUPLICATE KEY UPDATE to change the conflicting row.
  • In MySQL 8.0.19 and later, use an inserted-row alias such as incoming; avoid the deprecated VALUES(column) form in new code.
  • Check how multiple unique indexes affect which existing row MySQL updates.

For SQL Server’s transaction-based pattern and MERGE trade-offs, see SQL Server UPSERT.