UPSERT¶

What is Upsert in SQL?¶

UPSERT is one of the basic functions of database management systems in managing databases. It is a combination of UPDATE and INSERT. It allows the database operation language to insert a new data into a table or update existing data. When UPSERT operates on a new data, the INSERT operation is triggered. If the record already exists in the table, UPSERT is similar to the UPDATE statement.

For example, we have a student table with id column as the primary key:

> desc student;
+-----------------------+---------+--------------------------------------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra | Comment |
+-----------------------+---------+--------------------------------------------------------------------------------------------
| id | INT(32) | NO | PRI | NULL | | | |
| name | VARCHAR(50) | YES | | NULL | | | |
+-----------------------+---------+--------------------------------------------------------------------------------------------

When changing student information in this table, we can use upsert. Logically speaking it is like this:

If the student id exists in the table, update the row with new information.
If the student does not exist in the table, add it as a new row.

However, the UPSERT command does not exist in Matrixone, but it still implements UPSERT. By default, Matrixone provides the following three ways to implement Matrixone UPSERT operations:

INSERT IGNORE¶

When we insert illegal rows into the table, the INSERT IGNORE statement ignores the error at execution. For example, the primary key column does not allow us to store duplicate values. When we use INSERT to insert a data into the table, and the primary key of this data already exists in the table, the Matrixone server generates an error and the statement execution fails. However, when we use INSERT IGNORE to execute this statement, the Matrixone server will not generate an error.

REPLACE¶

In some cases, we want to update the data that already exists. You can use REPLACE at this time. When we use the REPLACE command, the following two situations may occur:

If there is no corresponding record in the database, execute the standard INSERT statement.
If there is a corresponding record in the database, the REPLACE statement will first delete the corresponding record in the database and then execute the standard INSERT statement (this update operation is performed when the primary key or unique index is repeated)

In the REPLACE statement, updating data is divided into two steps: first delete the original record, and then insert the record to be updated.

INSERT ON DUPLICATE KEY UPDATE¶

So far, we’ve seen two UPSERT commands, but they all have some limitations. INSERT ON DUPLICATE KEY IGNORE simply ignores duplicate error. REPLACE will detect INSERT error, but it will delete the original data before adding new data. Therefore, we still need a better solution.

INSERT ON DUPLICATE KEY UPDATE is a better solution, it does not delete duplicate rows, and when we use the ON DUPLICATE KEY UPDATE sub-statement in a SQL statement and there is a row of data that producesduplicate error` on the primary key or unique index, it will update on the existing data.