What Is UPSERT and How To Use It in MySQL?

What Is UPSERT and How To Use It in MySQL?

With databases, one can easily store and work with data, thereby reducing error. It has become a significant part of today’s society. From small size companies to big multi-national organizations, everyone is using databases for storing information. As the amount of data grows, storing the information in a spreadsheet in an organized manner is practically impossible, as this might lead to a lot of redundancies and unnecessary data being stored. Many times the data might also conflict, which is very harmful to any business. Hence, storing the data in a database is the best possible suction can think of.

Talking about data the Database Management System, or DBMS, is software that offers users a better way to manage their databases. Various crucial features are offered by the Database Management System, of which UPSERT is one of the prime features.

The UPSERT can be defined as the combination of two words, Update and Insert. Here, the first two letters are derived from Update while the rest are from Insert. UPSERT is termed a Data Manipulation Language where one can insert a new row or update an existing row. With UPSERT, only one operation can be handled at one time; that is, it is an atomic operation. By default, MySQL offers an ON DUPLICATE KEY UPDATE option over the INSERT, which helps to perform exactly the same task as UPSERT. While other statements can also be implemented for completing the task, such as statements like ignoring, REPLACE, or INSERT.

In this article, we shall be exploring four ways by which one can perform MySQL UPSERT operation, helping them easily add a new row to the existing database or update an existing row quickly.

MySQL UPSERT Example

As studied, UPSERT is a crucial feature of the Database Management System software that helps to manage the database. It falls under the Data Manipulation Language, where users can insert new records or update the existing data into a table. As the UPSERT statement is made by a combination of two words, namely UPDATE and INSERT, it has the ability to perform both the functionalities offered by UPDATE and INSERT statements in MySQL UPSERT.

Being an atomic operation, MySQL UPSERT gets completed in a single step. For instance, for a new record, it triggers an INSERT command. But, for an already existing record, it performs the UPDATE statement.

MySQL offers an ON DUPLICATE KEY UPDATE option as part of the INSERT, at default, that accomplishes the UPSERT task. While with INSERT, IGNORE, or REPLACE in MySQL, you can fulfill a similar objective.

Let us explore the following four methods to perform MySQL UPSERT operation as discussed below:

• MySQL UPSERT using INSERT IGNORE
• MySQL UPSERT using REPLACE
• MySQL UPSERT using INSERT ON DUPLICATE KEY UPDATE
• MySQL UPSERT vs. INSERT ON CONFLICT

UPSERT using INSERT IGNORE

In this section, we shall be learning how we can implement the MySQL UPSERT using INSERT IGNORE.

One can use the INSERT IGNORE statement to ignore the errors at the time of execution when an INSERT operation is performed on rows that are invalid. For instance, if we are trying to store duplicate values for the same column having the primary key with the INSERT IGNORE statement, it shall throw an error. The INSERT IGNORE statement does not let you store the exact same primary key twice that is existing already in the table, and you should be encountering an error if done so. However, a warning instead of an error gets generated when we perform this action with the INSERT IGNORE command.

Syntax

The syntax for INSERT IGNORE statement that could be used for MySQL UPSERT as seen below:

Let us learn more about the INSERT IGNORE statement in MySQL UPSERT via a code example.

Here, we start by creating a table named "School" as seen in the code above.

Code:

Once you execute the SELECT statement, you shall see three rows as seen below:

Output:

Explanation:
We will have four columns in the table School. Here, we have specified the Students_Name with the UNIQUE constraint to make sure that no value is duplicated in this column. Then we need to have a certain number of records in the table. We used the INSERT code to do so. Here we inserted three rows into the School table. To validate if the INSERT statement has feed the information in the School table, we make use of the SELECT statement as seen in the code above.

Now as we have the database ready we are going to add two new records to the School table:

Code:

Output:

Now, by implementing the INSERT IGNORE INTO statement, we can overcome the ERROR.

Code:

Output:

Explanation:
As seen in the code above, when we run the code to add two new records into the School table, we see that we get the error: 'ERROR 1062 (23000): Duplicate entry' 'James' for key 'school.Students_Name' because the Students_Name column is violating the UNIQUE constraint. This is where the implementation of MySQL UPSERT via the INSERT IGNORE command can help to resolve the issue.

As seen when we use the INSERT IGNORE command for the same records, but now we don't see any error for violation of the Students_Name column. Instead, only a warning is received. When you execute the INSERT IGNORE command, MySQL will simply produce a message: something like one row was added, and the other row was ignored. For more detailed warnings, one can use the SHOW WARNINGS command, as seen in the code above, to dive deeper into the warning thrown.

UPSERT using REPLACE

In this section, we shall be learning how we can implement MySQL UPSERT using REPLACE.

For scenarios where we are looking to update the already existing records in the table so that they are kept updated, we can use the MySQL UPSERT with the REPLACE statement. When we implement the typical INSERT query for a scenario like this, it shall throw an error like you have an entry that is duplicated for the PRIMARY KEY. Hence, you can avoid that error by implementing the REPLACE statement for performing the task.

Two possible cases could happen when you use the REPLACE command, as seen below:

• When in the existing row no matching value is found against this, then the normal INSERT statement could be easily performed.
• When the already existing record matches the new record, you can implement the REPLACE command to delete the old row, after which you can easily use the standard INSERT statement that, once executed, would add the new added record to the table.

Hence, we can say that with the help of the REPLACE statement, the table gets updated and updation happens in two steps. First, the old record is deleted. Second, the new freshly updated record is added, alike to how INSERT will add a new record. Therefore, the REPLACE statement implements two typical functions , which are, DELETE and INSERT.

Syntax

The syntax for the REPLACE statement that could be used for MySQL UPSERT as seen below:

Let us learn more about the REPLACE statement in MySQL UPSERT via a code example. Here, we should take the same table we created before, the School table.

Code:

Output:

Explanation:
As seen in the example above, we use the REPLACE command to update the existing value for the Class_ID number 5. Here we are updating the subject from Physics to Hindi using the REPLACE statement. When the updation happens, you will see that the message the terminal shall display is "2 row(s) affected". You might think we only made changes for one row, then why were two rows affected? It is so as we studied, when we implement the REPLACE command first, the record gets deleted. After this, a new record is added to the table.

UPSERT using INSERT On Duplicate Key Update

In this section, we shall be learning how we can implement the MySQL UPSERT using INSERT IGNORE.

So far, we have studied MySQL UPSERT using INSERT IGNORE and MySQL UPSERT using REPLACE statements. Though both help to update the existing record and add a new record, both have their own set of limitations. With the INSERT IGNORE statement, one can only get warnings and prevent errors by ignoring the error with the duplication, but it does not make any modification in the table. While with the REPLACE method has resolved this error by first detecting the error with the typical INSERT statement. It then deletes the row first, after which it adds the newly updated record. Therefore, we still need a complete solution that can be a more clarified solution.

With the INSERT ON DUPLICATE KEY UPDATE statement, one can apply the logic of MySQL UPSERT, but it will not delete the duplicate row. With this non-destructive method, the INSERT ON DUPLICATE KEY UPDATE statement effectively updates the value based on the condition we define for the ON DUPLICATE KEY UPDATE clause in the SQL statement. Once done, no duplicate error value in the row is caused for the value in a UNIQUE or PRIMARY KEY index column. After this, the update over the existing row occurs.

Syntax

The syntax for INSERT ON DUPLICATE KEY UPDATE statement that could be used for MySQL UPSERT as seen below:

Let us learn more about the INSERT ON DUPLICATE KEY UPDATE statement in MySQL UPSERT via a code example. Here, we should take the same table we created before the School table.

Code:

Output:

Explanation:
As seen in the example above, we use the INSERT statement to add a new row having the Class_ID as 55 and the Subject as English. When we execute the command, it shall not output any error as there is no conflict of overlapping duplicate values. We then execute the MySQL UPSERT using the INSERT ON DUPLICATE KEY UPDATE statement that, as seen, has updated the duplicate record for the mentioned Class_ID column.

Once we run the command, we see the successful message popping up that says two rows are affected. Also, in the output, we see that Class_ID=55 already exists, and the value of the Subjects gets updated from English to French.

UPSERT vs. INSERT ON CONFLICT

Now, in this section, we shall understand how we can leverage the comparison between the UPSERT vs. INSERT ON CONFLICT statement in MySQL.

As studied so far, with the help of the UPSERT command one can update or insert depending upon the uniqueness of the primary key column or columns, and accordingly the UPDATE or INSERT are executed.

As we do not need to explicitly define the conflict target or action while implementing the UPSERT statement, it becomes much more straightforward than INSERT ON CONFLICT.

With the INSERT ON CONFLICT statement for handling the upserts in situations where scenario requires us to avoid conflicts which are not related to the primary key. For instance, the conflict target is pre-defined as the foreign key column. And it usually, the specifics of the workloads, that helps to clearly differentiate between UPSERT and INSERT ON CONFLICT statement.