This tutorial explains about MySQL UPSERT command with the help of simple examples. An upsert is a smart operation that turns into an INSERT or UPDATE whichever is applicable. Also, it is an atomic transaction, which means completed in a single step. Let’s understand – If a record is new, then UPSERT triggers an INSERT. But, if it already exists, then UPSERT performs an UPDATE.
MySQL provides the ON DUPLICATE KEY UPDATE option to INSERT, which accomplishes this behavior. However, there are other statements like INSERT IGNORE or REPLACE, which can also fulfill this objective. We’ll discuss and see all these solutions in this post today.
MySQL UPSERT with Examples
We can imitate MySQL UPSERT in one of these three ways:
1. UPSERT using INSERT IGNORE
2. UPSERT using REPLACE
3. UPSERT using INSERT with ON DUPLICATE KEY UPDATE
However, before you go on reading more, let’s create a sample, and insert some dummy data. It’ll help us explain the concept through examples.
-- Creating a sample table for testing purpose
CREATE TABLE BLOGPOSTs
(
postId INT NOT NULL,
postTitle VARCHAR(60) PRIMARY KEY,
postPublished DATE
);
-- Inserting some data with dummy values
INSERT INTO BLOGPOSTs
(
postId, postTitle, postPublished
)
VALUES
(1, 'Python Tutorial', '2019-07-21'),
(2, 'CSharp Tutorial', '2019-07-14'),
(3, 'MySQL Tutorial', '2019-08-01'),
(4, 'Java Tutorial', '2019-08-05');
-- Print all rows
SELECT
*
FROM
BLOGPOSTs;Let’s see each of the above in action below.
1. UPSERT using INSERT IGNORE
When we use INSERT IGNORE for adding a record, it gets through even if there are errors in performing INSERT. So, if the target table already has a row with a matching UNIQUE or PRIMARY key, then INSERT REPLACE would suppress all possible errors.
Moreover, it skips the INSERT operation altogether. Such kind of statement is useful when we need to add a large number of records in one go. And, the table may already have a part of that data inside. Let’s test this behavior with the help of an example. But before that, you may have a quick look at this diagram.
Here, we are trying to use a duplicate record using the standard MySQL INSERT statement.
-- Inserting duplicate record
INSERT INTO BLOGPOSTs
(
postId, postTitle, postPublished
)
VALUES
(5, 'Python Tutorial', '2019-08-04');The above statement fails with the following error:
Duplicate entry 'Python Tutorial' for key 'PRIMARY'
Since the <postTitle> is a primary key, we can’t have another record having the same value for this field. However, if we do the same operation using the INSERT IGNORE, then it will ignore the error.
Let’s see how it works:
-- Inserting duplicate record
INSERT IGNORE INTO BLOGPOSTs
(
postId, postTitle, postPublished
)
VALUES
(5, 'Python Tutorial', '2019-08-04');
-- Print all rows
SELECT
*
FROM
BLOGPOSTs;This time, INSERT won’t cause any error and SELECT will print all the records.
1 Python Tutorial 2019-07-21 2 CSharp Tutorial 2019-07-14 3 MySQL Tutorial 2019-08-01 4 Java Tutorial 2019-08-05
2. UPSERT using REPLACE
There come situations when we have to replace some rows even if INSERT could fail due to duplicate values of the primary key field. Hence, we should use the REPLACE statement for such cases.
However, if we opt to use REPLACE, then it could result in one of the following outcomes:
- If we don’t face any error, then REPLACE would behave as a regular INSERT command.
- If a duplicate record exists, then REPLACE would first delete it and perform the INSERT subsequently.
Here is a diagram showing the flow of steps in upsert sing replace.
Let’s run our previous test done in #1 again here and observe its result.
-- Replacing duplicate record
REPLACE INTO BLOGPOSTs
(
postId, postTitle, postPublished
)
VALUES
(5, 'Python Tutorial', '2019-08-04');
-- Print all rows
SELECT
*
FROM
BLOGPOSTs;The above REPLACE statement added a new row after deleting the duplicate one. Please crosscheck this from the output below.
5 Python Tutorial 2019-08-04 2 CSharp Tutorial 2019-07-14 3 MySQL Tutorial 2019-08-01 4 Java Tutorial 2019-08-05
You can check the below record that appeared after replacing the old one.
-- The old entry was: 1 Python Tutorial 2019-07-21 -- The new entry is: 5 Python Tutorial 2019-08-04
3. UPSERT using INSERT with ON DUPLICATE KEY UPDATE
We have seen the two UPSERT commands so far. However, each method had some limitations. The first one INSERT IGNORE only ignores the duplicate error, but does not make any modification to the table.
The second method, REPLACE, looked a bit more promising. It detected the INSERT error but required the deletion of the row before adding the new record. Hence, we are still waiting for a more refined solution until now.
So, here comes the INSERT … ON DUPLICATE KEY UPDATE statement. It is non-destructive, which means it doesn’t have to drop the duplicate row. Instead, it issues an UPDATE whenever it finds a matching record having the same UNIQUE or PRIMARY KEY value.
The following diagram sums up the steps to perform an upsert using the insert command with a duplicate key constraint.
Let’s now validate the feasibility of the third method with the help of a real example:
-- Updating duplicate record
INSERT INTO BLOGPOSTs
(
postId, postTitle, postPublished
)
VALUES
(5, 'Python Tutorial', '2019-08-04')
ON DUPLICATE KEY UPDATE
postId = 5, postTitle = 'Python Tutorial', postPublished = '2019-08-04';
-- Print all rows
SELECT
*
FROM
BLOGPOSTs;The above MySQL UPSERT command updated the duplicate record. You can crosscheck from the below result set:
5 Python Tutorial 2019-08-04 2 CSharp Tutorial 2019-07-14 3 MySQL Tutorial 2019-08-01 4 Java Tutorial 2019-08-05
The following is the detail of the row that went through modification:
-- The old entry was: 1 Python Tutorial 2019-07-21 -- The new entry is: 5 Python Tutorial 2019-08-04
Read more from here: What is the difference between UPSERT and insert in MySQL?
Summary – MySQL UPSERT
We hope that after wrapping up this tutorial, you will feel comfortable using the MySQL UPSERT commands. However, you may practice more with examples to gain confidence.
Also, to learn SQL from scratch to depth, read our step-by-step MySQL tutorial.
