Khóa học miễn phí MySQL – Upsert nhận dự án làm có lương

MySQL – Upsert

Table of content


The MySQL UPSERT Operation

The MySQL UPSERT operation combines INSERT and UPDATE into a single statement, allowing you to insert a new row into a table or update an existing row if it already exists. We can understand in the name (UPSERT) itself, where UP stands for UPDATE and SERT stands for INSERT.

This tutorial covers three common methods to perform UPSERT operations in MySQL: INSERT IGNORE, REPLACE, and INSERT with ON DUPLICATE KEY UPDATE.

UPSERT Using INSERT IGNORE

The INSERT IGNORE statement in MySQL allows you to insert a new record into a table. If a record with the same primary key already exists, it ignores the error and doesn”t insert the new record.

Example

First, let us create a table with the name COURSES using the following query −

CREATE TABLE COURSES(
   ID int,
   COURSE varchar(50) primary key,
   COST int
);

Here, we are inserting records into the COURSES table −

INSERT INTO COURSES VALUES 
(1, "HTML", 3000),
(2, "CSS", 4000),
(3, "JavaScript", 6000),
(4, "Node.js", 10000),
(5, "React.js", 12000),
(6, "Angular", 8000),
(7, "Php", 9000);

The COURSES table obtained is as follows −

ID COURSE COST
6 Angular 8000
2 CSS 4000
1 HTML 3000
3 JavaScript 6000
4 Node.js 10000
7 Php 9000
5 React.js 12000

Now, we attempt to insert a duplicate record using the INSERT INTO statement in the following query −

INSERT INTO COURSES VALUES (6, ''Angular'', 9000);

This results in an error because a duplicate record cannot be inserted −

ERROR 1062 (23000): Duplicate entry ''Angular'' for key ''courses.PRIMARY''

Using INSERT IGNORE −

Now, let us perform the same operation using INSERT IGNORE statement −

INSERT IGNORE INTO COURSES VALUES (6, ''Angular'', 9000);

Output

As we can see in the output below, the INSERT IGNORE statement ignores the error −

Query OK, 0 rows affected, 1 warning (0.00 sec)

Verification

We can verify the COURSES table to see that the error was ignored using the following SELECT query −

SELECT * FROM COURSES;

The table obtained is as follows −

ID COURSE COST
6 Angular 8000
2 CSS 4000
1 HTML 3000
3 JavaScript 6000
4 Node.js 10000
7 Php 9000
5 React.js 12000

UPSERT Using REPLACE

The MySQL REPLACE statement first attempts to delete the existing row if it exists and then inserts the new row with the same primary key. If the row does not exist, it simply inserts the new row.

Example

Let us replace or update a row in the COURSES table. If a row with COURSE “Angular” already exists, it will update its values for ID and COST with the new values provided. Else, a new row will be inserted with the specified values in the query −

REPLACE INTO COURSES VALUES (6, ''Angular'', 9000);

Output

The output for the query above produced is as given below −

Query OK, 2 rows affected (0.01 sec)

Verification

Now, let us verify the COURSES table using the following SELECT query −

SELECT * FROM COURSES;

We can see in the following table, the REPLACE statement added a new row after deleting the duplicate row −

ID COURSE COST
6 Angular 9000
2 CSS 4000
1 HTML 3000
3 JavaScript 6000
4 Node.js 10000
7 Php 9000
5 React.js 12000

UPSERT Using INSERT with ON DUPLICATE KEY UPDATE

The INSERT … ON DUPLICATE KEY UPDATE statement in MySQL attempts to insert a new row. If the row already exists, it updates the existing row with the new values specified in the statement.

Example

Here, we are updating the duplicate record using the following query −

INSERT INTO COURSES VALUES (6, ''Angular'', 9000)
ON DUPLICATE KEY UPDATE
ID = 6, COURSE = ''Angular'', COST = 20000;

Output

As we can see in the output below, no error is generated and the duplicate row gets updated.

Query OK, 2 rows affected (0.01 sec)

Verification

Let us verify the COURSES table using the following SELECT query −

SELECT * FROM COURSES;

As we can see the table below, the INSERT INTO… ON DUPLICATE KEY UPDATE statement updated the duplicate record −

ID COURSE COST
6 Angular 20000
2 CSS 4000
1 HTML 3000
3 JavaScript 6000
4 Node.js 10000
7 Php 9000
5 React.js 12000

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc