MySQL UPSERT | INSERT or UPDATE Whichever is Applicable

This tutorial explains about MySQL UPSERT command with the help of simple examples. An upsert is a smart operation that turns into INSERT or UPDATE whichever is applicable. Also, it is an atomic transaction, which means complete 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.

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, so 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 would 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 regular INSERT command.
• If a duplicate record exists, then REPLACE would first delete it and perform the INSERT subsequently.

Let’s run our previous test done in #1 again here and observes 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 saw the two UPSERT commands so far. However, each method had some limitations. The first one INSERT IGNORE was only ignoring the duplicate error, but not making 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 wandering 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.

Let’s 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

Summary – MySQL UPSERT

We hope that after wrapping up this tutorial, you should 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, do read our step-by-step MySQL tutorial.