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