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
orUPDATE
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 to899.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.