MariaDB UPSERT with ON DUPLICATE KEY UPDATE

MariaDB implements UPSERT behavior with INSERT ... ON DUPLICATE KEY UPDATE. The statement inserts a row when its primary or unique key is new. If the insert conflicts with a primary key or unique index, MariaDB updates the existing row instead. The MariaDB reference documents the syntax and its conflict behavior.

Create a table with a unique key

The key determines when an insert counts as a duplicate. This example uses the SKU as the primary key:

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

Insert a row or update the existing row

Use VALUES(column_name) in the update clause to refer to the value from the row being inserted:

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

If BK-15 is new, MariaDB inserts the row. If that SKU already exists, it replaces the stored product name and quantity with the proposed values. MariaDB documents VALUES() for this use in its VALUES reference.

To add the incoming quantity to the value already stored, use the existing column together with the proposed value:

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

If BK-15 already has a quantity of 5, this statement updates it to 8. Use quantity = VALUES(quantity) instead when the incoming quantity should replace the stored quantity.

Upsert multiple rows

The same clause works with a multi-row VALUES list. MariaDB checks each proposed row against the table’s primary and unique keys:

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

New SKUs are inserted. A row with an existing SKU follows the update expressions.

Upsert rows selected from another table

You can also insert a query result and handle key conflicts in the same statement:

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

The SELECT provides the proposed rows. The update clause uses VALUES(column_name) to refer to the corresponding value from each proposed row. See MariaDB’s INSERT ... SELECT reference.

Which key triggers the update?

MariaDB does not provide a conflict-target clause such as ON CONFLICT (sku). Any primary-key or unique-index conflict can trigger the update. If more than one unique index matches, MariaDB updates only the first matching row; the documentation advises against this statement on tables with multiple unique indexes. Design the table so the conflict path is unambiguous.

MariaDB reports one affected row for an insert and two for an update, unless the client uses the CLIENT_FOUND_ROWS flag. The affected-row count is useful when a client needs to distinguish the two paths.

Avoid common mistakes

• No unique key: without a primary key or unique index, the statement has no duplicate key to detect and behaves like a normal insert.
• Several matching unique indexes: MariaDB updates only the first matching row. Avoid relying on which row wins when different unique indexes conflict with different rows.
• Treating REPLACE as an update: REPLACE deletes the conflicting row and inserts a new row. That can have different effects on auto-increment values, triggers, and foreign keys. See MariaDB’s REPLACE reference.
• Using INSERT IGNORE as the same operation: INSERT IGNORE converts certain errors to warnings and skips conflicting rows; it does not update the existing row. See INSERT IGNORE.

For equivalent syntax in other databases, see MySQL UPSERT, PostgreSQL INSERT ON CONFLICT, SQLite UPSERT, and SQL Server UPSERT.

For a syntax comparison that also covers Oracle, see SQL UPSERT Syntax by Database.