# 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:

```text
> 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](insert-ignore.md)

- [INSERT ON DUPLICATE KEY UPDATE](insert-on-duplicate.md)

- [REPLACE](replace.md)

## 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 produces`duplicate error` on the primary key or unique index, it will update on the existing data.