How to Insert or Update Rows in MySQL with ON DUPLICATE KEY UPDATE

When you insert a row into a MySQL table, you may need to add it if no row has its key, or update the row that already has that key. MySQL handles both outcomes in one statement with INSERT ... ON DUPLICATE KEY UPDATE. This statement performs an upsert, meaning “insert or update.”

A duplicate occurs when the new row repeats a value that a PRIMARY KEY or UNIQUE index requires to be unique. MySQL then updates the matching row instead of inserting another one. You choose which columns the update changes, so the statement works well for importing records or refreshing stored values.

Insert new rows and update duplicates

Suppose inventory.sku has a unique index. The following statement inserts a product when its SKU is new and replaces the stored quantity when that SKU already exists:

INSERT INTO inventory (sku, quantity, updated_at)
VALUES ('A-100', 12, CURRENT_TIMESTAMP) AS incoming
ON DUPLICATE KEY UPDATE
    quantity = incoming.quantity,
    updated_at = incoming.updated_at;

The alias incoming names the row MySQL would have inserted. The update clause reads values from that row to set the existing row’s quantity and timestamp. MySQL chooses the existing row based on the primary or unique key that triggered the duplicate.

The MySQL reference manual documents this behavior and the row-alias syntax. Use row aliases in MySQL versions that support them; MySQL deprecated VALUES(column) in this clause. Older MySQL versions use syntax such as quantity = VALUES(quantity); check the server version before choosing between forms.

Choose exactly which columns change

The update clause controls what happens to an existing row. Columns omitted from that clause keep their current values. For example, if a row also contains a status column, the statement above leaves that status unchanged.

You can also make an update conditional. This example keeps an existing suppressed status while allowing other statuses to be replaced with the incoming value:

INSERT INTO subscriptions (email, status)
VALUES ('reader@example.com', 'active') AS incoming
ON DUPLICATE KEY UPDATE
    status = IF(
        subscriptions.status = 'suppressed',
        subscriptions.status,
        incoming.status
    );

IF(condition, value_if_true, value_if_false) returns one of two values based on the condition. Here, MySQL preserves the existing status when it is suppressed; otherwise, it uses the incoming status. Conditional assignments like this are useful when a particular stored value must take precedence over imported data. A worked conditional-update example shows the same general approach.

Upsert several rows in one statement

You can provide multiple rows in the VALUES clause and use one update clause for all of them:

INSERT INTO inventory (sku, quantity)
VALUES
    ('A-100', 12),
    ('B-200', 7)
AS incoming
ON DUPLICATE KEY UPDATE
    quantity = incoming.quantity;

MySQL inserts each new SKU and updates the quantity for each duplicate SKU. A prepared statement uses placeholders to mark values supplied later. Provide one for every value in every row, then bind the values in the same order. For example, two rows with two columns require four value placeholders.

Check affected rows and unique keys

MySQL reports affected rows for the statement. An inserted row reports 1, and an updated row reports 2. MySQL reports 0 when an existing row already has the requested values. If the connection uses the CLIENT_FOUND_ROWS flag, the last case reports 1 instead of 0. Code that interprets this count should account for that connection setting.

Be careful when the table has more than one unique index. The new row’s values can match one existing row under one unique key and a different row under another. MySQL updates only one of those rows, which can produce an unsafe result when the keys identify different records. The MySQL manual advises against using this statement on tables with multiple unique indexes unless you know which conflict MySQL will handle.

An InnoDB table with an AUTO_INCREMENT column also consumes an auto-increment value when an insert attempt becomes an update. As a result, the sequence can have gaps even though no new row was added. Do not treat consecutive auto-increment values as proof that every insert created a row.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com