Using INSERT … ON DUPLICATE KEY UPDATE in MySQL

If you need to do some inserts and updates in MySQL, one option is to run separate INSERT and UPDATE statements. Another option is to use the INSERT ... ON DUPLICATE KEY UPDATE statement. Depending on the scenario, this can be a handy option.

How it Works

The INSERT ... ON DUPLICATE KEY UPDATE statement is kind of like a cross between an INSERT and an UPDATE operation. It inserts or updates rows depending on whether there’s a duplicate in the table already.

Here’s a basic overview of how it works:

  • Inserting or Updating Rows: If there’s a unique key conflict (e.g., duplicate primary key or unique index), the UPDATE portion is triggered. Otherwise a new row will be inserted.
  • Customizable Updates: You can choose which columns to update and even use expressions to modify the existing data.
  • Improved Performance: This statement is more efficient than manually checking for the existence of a row, followed by INSERT or UPDATE separately.

Therefore, INSERT ... ON DUPLICATE KEY UPDATE can be useful for handling scenarios where you want to either insert a new row or update the existing one when conflicts arise.

Syntax

The basic syntax goes like this:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2, ...;

So it’s like a normal INSERT statement, but with the ON DUPLICATE KEY UPDATE part tagged on to the end.

Example

Let’s walk through a full example, including table creation, data insertion, and usage of INSERT ... ON DUPLICATE KEY UPDATE.

Step 1: Create Sample Table & Insert Data

We’ll create a Products table and insert some sample data:

-- Create table
CREATE TABLE Products (
    ProductID INT AUTO_INCREMENT PRIMARY KEY,
    ProductName VARCHAR(255) NOT NULL,
    Quantity INT NOT NULL,
    Price DECIMAL(10, 2) NOT NULL,
    UNIQUE (ProductName)
);

-- Insert initial data
INSERT INTO Products (ProductName, Quantity, Price)
VALUES 
('Laptop', 10, 999.99),
('Smartphone', 50, 499.99),
('Headphones', 100, 79.99);

-- Return initial data
SELECT * FROM Products;

Output:

+-----------+-------------+----------+--------+
| ProductID | ProductName | Quantity | Price |
+-----------+-------------+----------+--------+
| 1 | Laptop | 10 | 999.99 |
| 2 | Smartphone | 50 | 499.99 |
| 3 | Headphones | 100 | 79.99 |
+-----------+-------------+----------+--------+

Step 2: Use INSERT ... ON DUPLICATE KEY UPDATE

Now, we’ll attempt to insert a new row with the same ProductName (Laptop). Since ProductName is a unique key, this will trigger an update:

INSERT INTO Products (ProductName, Quantity, Price)
VALUES ('Laptop', 15, 899.99)
ON DUPLICATE KEY UPDATE
Quantity = Quantity + 15, Price = 899.99;

Output:

Query OK, 2 rows affected (0.01 sec)

Here’s what that statement does:

  • If the product doesn’t exist, this inserts it with the specified quantity and price.
  • If a product with the name 'Laptop' already exists (which it does), it increases its quantity by 15 and updates the price to 899.99.
  • Did you notice that it tells us that two rows were affected? More on this later.

For now, let’s check the table:

SELECT * FROM Products;

Result:

+-----------+-------------+----------+--------+
| ProductID | ProductName | Quantity | Price |
+-----------+-------------+----------+--------+
| 1 | Laptop | 25 | 899.99 |
| 2 | Smartphone | 50 | 499.99 |
| 3 | Headphones | 100 | 79.99 |
+-----------+-------------+----------+--------+

Notice that the ProductID (auto-increment column) for the Laptop remains the same because the row was updated, not inserted. But that’s not to say that the auto-increment column wasn’t incremented. It was. The next example will confirm this for us.

Step 3: Insert a New Product

Let’s insert a new product that doesn’t have a duplicate. We can still specify the ON DUPLICATE KEY UPDATE part to specify what to do in the case that there is a duplicate. It just won’t be needed in this case.

Do the insert and select the result:

-- Insert product
INSERT INTO Products (ProductName, Quantity, Price)
VALUES ('Tablet', 20, 299.99)
ON DUPLICATE KEY UPDATE
Quantity = Quantity + 20, Price = 299.99;

-- Check the result
SELECT * FROM Products;

Result:

+-----------+-------------+----------+--------+
| ProductID | ProductName | Quantity | Price |
+-----------+-------------+----------+--------+
| 1 | Laptop | 25 | 899.99 |
| 2 | Smartphone | 50 | 499.99 |
| 3 | Headphones | 100 | 79.99 |
| 5 | Tablet | 20 | 299.99 |
+-----------+-------------+----------+--------+

This produced the same result as doing a regular INSERT without the ON DUPLICATE KEY UPDATE part.

Notice that the ProductID field has skipped a value – it went from 3 to 5, skipping 4. That’s because back in our previous example, the INSERT ... ON DUPLICATE KEY UPDATE statement resulted in an update (instead of an insert).

For InnoDB tables, when INSERT ... ON DUPLICATE KEY UPDATE results in an update, what happens is that it first tries to insert the row (thus, incrementing the AUTO_INCREMENT column), then if it finds that it’s a duplicate, it updates the existing row. This results in an unused AUTO_INCREMENT value. That’s why we saw that 2 rows had been affected when we ran that first example, even though our table showed that only one row had changed.

We would have seen a different result if we’d used a simple UPDATE statement, like this:

UPDATE Products
SET Quantity = Quantity + 15, Price = 899.99
WHERE ProductName = 'Laptop';

In that case the AUTO_INCREMENT column would not have been incremented.

More Info

See MySQL’s documentation for a more detailed explanation on the INSERT ... ON DUPLICATE KEY UPDATE statement.