MySQL UPSERT | Three Techniques to Perform an UPSERT – MPS


MySQL UPSERT | Three Techniques to Perform an UPSERT – mps

This tutorial explains about MySQL UPSERT command with the assistance of easy examples. An upsert is a sensible operation which turns into INSERT or UPDATE whichever is relevant. Also, it is an atomic transaction, 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 with Examples

We can imitate MySQL UPSERT in 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 clarify the idea via examples.

-- Creating a pattern desk for testing objective
CREATE TABLE BLOGPOSTs
    (
        postId INT NOT NULL,
        postTitle VARCHAR(60) PRIMARY KEY,
        postPublished DATE
    );

-- Inserting some information 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 every of the above in motion under.

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 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 postTitle is a main key, so we will’t 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’t 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. UPSERT utilizing 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 in 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, 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.

MYSQL Recommended put up:

MySQL UPSERT | Three Techniques to Perform an UPSERT
Source link

Have any Question or Comment?

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories

You have successfully subscribed to myprogrammingschool

There was an error while trying to send your request. Please try again.

My Programming School will use the information you provide on this form to be in touch with you and to provide updates and marketing.