MySQL UPSERT | Three Techniques to Perform an UPSERT – MPS

In this tutorial, we will explain you about the MySQL UPSERT command with the assistance of easy examples. An upsert is a sensible operation that turns into INSERT or UPDATE whichever is relevant. Also, it is an atomic transaction, which means full in a single step. Let’s perceive – If a document is new, then UPSERT triggers an INSERT. But, if it already exists, then UPSERT performs an UPDATE.

MySQL offers the ON DUPLICATE KEY UPDATE choice to INSERT, which accomplishes this conduct. However, there are different statements like INSERT IGNORE or REPLACE, which may also fulfill this goal. We’ll talk about and see all these options in this put up immediately.

MySQL Upsert Query Example

We can imitate MySQL UPSERT is considered one of these 3 ways:

1. UPSERT utilizing INSERT IGNORE
2. UPSERT utilizing REPLACE
3. UPSERT utilizing INSERT with ON DUPLICATE KEY UPDATE

However, earlier than you go on studying more, let’s create a pattern, and insert some dummy information. It’ll assist us to clarify the idea via examples.

Let’s see every of the above in motion.

1. UPSERT utilizing INSERT IGNORE

When we use INSERT IGNORE for including a document, it will get via even if there are errors in performing INSERT. So, if the goal desk already has a row with an identical UNIQUE or PRIMARY key, then INSERT REPLACE would suppress all attainable errors.

Moreover, it skips the INSERT operation altogether. Such sort of assertion is helpful once we want to add numerous pieces of information in one go. And, the desk might have already got part of that information inside. Let’s take a look at this conduct with the assistance of an instance.

Here, we try to use a reproduction document utilizing the usual MySQL INSERT assertion.

-- Inserting duplicate document
INSERT INTO BLOGPOSTs
    (
        postId, postTitle, postPublished
    )
VALUES
    (5, 'Python Tutorial', '2019-08-04');

The above assertion fails with the next error:

Duplicate entry 'Python Tutorial' for key 'PRIMARY'

Since the post title is the main key, so we will have one other document having the identical worth for this area. However, if we do the identical operation utilizing the INSERT IGNORE, then it is going to ignore the error.

Let’s see the way it works:

-- Inserting duplicate document
INSERT IGNORE INTO BLOGPOSTs
    (
        postId, postTitle, postPublished
    )
VALUES
    (5, 'Python Tutorial', '2019-08-04');

-- Print all rows
SELECT 
    *
FROM
    BLOGPOSTs;

This time, INSERT received any trigger any error, and SELECT would print all of the information.

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. MySQL UPSERT REPLACE

There come conditions when we’ve to substitute some rows even if INSERT may fail due to duplicate values of the first key area. Hence, we must always use the REPLACE assertion for such circumstances.

However, if we decide to use REPLACE, then it may end result is one of many following outcomes:

• If we don’t face any error, then REPLACE would behave as common INSERT command.
• If a reproduction document exists, then REPLACE would first delete it and carry out the INSERT subsequently.

Let’s run our earlier take a look at accomplished in #1 once more right here and observes its end result.

-- Replacing duplicate document
REPLACE INTO BLOGPOSTs
    (
        postId, postTitle, postPublished
    )
VALUES
    (5, 'Python Tutorial', '2019-08-04');

-- Print all rows
SELECT 
    *
FROM
    BLOGPOSTs;

The above REPLACE assertion added a new row after deleting the duplicate one. Please crosscheck this from the output under.

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 verify the under document appeared after changing the previous one.

-- The previous entry was:
1	Python Tutorial	2019-07-21
-- The new entry is:
5	Python Tutorial	2019-08-04

3. UPSERT utilizing INSERT with ON DUPLICATE KEY UPDATE

We noticed the 2 UPSERT instructions to date. However, every methodology had some limitations. The first one INSERT IGNORE was solely ignoring the duplicate error, however not making any modification to the desk.

The second methodology, REPLACE, appeared a bit more promising. It detected the INSERT error however required to delete the row earlier than including the new document. Hence, we’re nonetheless wandering for a more refined answer till now.

So, right here comes the INSERT … ON DUPLICATE KEY UPDATE assertion. It is non-harmful, which means it doesn’t have to drop the duplicate row. Instead, it points an UPDATE each time it finds an identical document having the identical UNIQUE or PRIMARY KEY worth.

Let’s validate the feasibility of the third methodology with the assistance of an actual instance:

-- Updating duplicate document
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 up to date the duplicate document. You can crosscheck from the under end 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 element of the row that went via modification:

-- The previous 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 must really feel comfy in utilizing the MySQL UPSERT instructions. However, you could apply more with examples to achieve confidence.

Also, to be taught SQL from scratch to depth, do learn our step-by-step MySQL tutorial.