MySQL - Upsert

MySQL - The UPSERT Operation

What is an UPSERT?

UPSERT is a portmanteau of UPDATE and INSERT. It’s a database operation that inserts a new row into a table if it doesn’t already exist, or updates the existing row if it does. This is a common requirement when synchronizing data, tracking statistics, or importing records.

To perform an upsert, the table must have a PRIMARY KEY or a UNIQUE index. This key is used by MySQL to determine if a row is a “duplicate”.

Let’s use a product_inventory table for our examples:

CREATE TABLE product_inventory (
    product_sku VARCHAR(50) PRIMARY KEY,
    quantity INT NOT NULL,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO product_inventory (product_sku, quantity)
VALUES
    ('LAPTOP-001', 25),
    ('MOUSE-002', 150);

Method 1: INSERT … ON DUPLICATE KEY UPDATE (Recommended)

This is the most flexible, powerful, and widely-used method for performing an upsert in MySQL. It attempts an INSERT, and if that fails due to a duplicate key violation, it executes the UPDATE clause instead.

Syntax

INSERT INTO table_name (col1, col2, ...)
VALUES (val1, val2, ...)
ON DUPLICATE KEY UPDATE
    col1 = new_val1, col2 = new_val2, ...;

Example: Updating Inventory

Let’s say we receive a new shipment. We want to update the quantity for ‘MOUSE-002’ and add a new product, ‘KEYBOARD-003’.

-- This will UPDATE the existing 'MOUSE-002' record
INSERT INTO product_inventory (product_sku, quantity) VALUES ('MOUSE-002', 200)
ON DUPLICATE KEY UPDATE quantity = 200;
-- Query OK, 2 rows affected

-- This will INSERT a new 'KEYBOARD-003' record
INSERT INTO product_inventory (product_sku, quantity) VALUES ('KEYBOARD-003', 75)
ON DUPLICATE KEY UPDATE quantity = 75;
-- Query OK, 1 row affected

A more powerful pattern is to use the VALUES() function, which refers to the values that would have been inserted:

-- Increment the quantity of an existing product by 50
INSERT INTO product_inventory (product_sku, quantity) VALUES ('LAPTOP-001', 50)
ON DUPLICATE KEY UPDATE quantity = quantity + VALUES(quantity);

Method 2: INSERT IGNORE

The INSERT IGNORE statement attempts to insert a row, but if a duplicate key error occurs, it silently ignores the error and discards the new row. It does not perform an update.

Use Case: Best for bulk-loading data where you want to prevent the entire operation from failing if some duplicate records exist. You only want to insert what’s new.

Example

-- This will be ignored because 'LAPTOP-001' already exists.
INSERT IGNORE INTO product_inventory (product_sku, quantity) VALUES ('LAPTOP-001', 999);
-- Query OK, 0 rows affected, 1 warning

-- The original quantity for 'LAPTOP-001' remains unchanged.

Method 3: REPLACE

The REPLACE statement works like INSERT, but if an old row has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

Warning: Use REPLACE with caution. Because it performs a DELETE then an INSERT, it has significant side effects.

Example

-- This will DELETE the existing 'MOUSE-002' record and INSERT a new one.
REPLACE INTO product_inventory (product_sku, quantity) VALUES ('MOUSE-002', 175);

Comparison: Which UPSERT Method Should You Use?

Method	Behavior on Duplicate	Side Effects	Best For
INSERT … ON DUPLICATE KEY UPDATE	Updates the existing row.	Minimal. AUTO_INCREMENT is not affected. Triggers fire correctly.	True upserts, synchronizing data, updating counters.
INSERT IGNORE	Skips the insert, no changes.	Minimal. Generates a warning instead of an error.	Bulk imports where you want to skip duplicates without stopping.
REPLACE	Deletes the old row, inserts a new one.	Significant. A new AUTO_INCREMENT ID is generated. DELETE and INSERT triggers fire. Foreign key cascades can occur.	Rare cases where a full record overwrite is intended and side effects are acceptable.

For the vast majority of cases, INSERT ... ON DUPLICATE KEY UPDATE is the safest, most predictable, and most powerful choice.

Common Pitfalls and Best Practices

• REPLACE Side Effects: Be very aware that REPLACE is a destructive operation. If other tables have foreign keys pointing to the row being replaced, the DELETE part of the operation could have cascading effects you did not intend.
• Multi-Column Unique Keys: All three methods work with multi-column UNIQUE keys. The duplicate check will be performed on the combination of all columns in the key.
• Check Affected Rows: After an INSERT ... ON DUPLICATE KEY UPDATE query, the number of affected rows returned is 1 for an insert, 2 for an update of an existing row (one delete-mark, one insert), and 0 if the new values are the same as the old values.