# What are Upserts? (/glossary-items/upserts)



An **upsert** is a database operation whose name combines "update" and "insert": it either updates an existing record if a matching one is found or inserts a new record if none exists. In a relational database management system (RDBMS), the in-built indexes make this straightforward, because they let the database quickly identify whether a specific record or entry already exists before deciding whether to update it or add a new row.

<Callout type="info">
  This article describes upsert functionality in general and is not specific to Firebolt.
</Callout>

## Understanding upserts [#understanding-upserts]

When dealing with large volumes of data, a relational database management system (RDBMS) helps users manage their database. It is this support system that offers key features to handle data. One feature of an RDBMS is the upsert. As the name suggests, the upsert command lets the user alter existing data by inserting a whole new row or just updating an existing one. A common scenario where an operation like upsert is useful is updating or expanding employee information in a database — for instance, adding a new row for an employee ID or changing employee contact details. Although the upsert is simply a command for an operation that must be executed, it is not universal: not all RDBMSs offer it as a command.

## Upsert examples [#upsert-examples]

### Updating an existing row [#updating-an-existing-row]

Here is an example of using upsert to update an existing table of employee details.

*SQL statement*

```sql
UPSERT INTO employees (id, name, email) VALUES (2, 'Shane', 'Shane@testxyzAsia.io');
```

*Current table*

![Current Table](https://cdn.prod.website-files.com/5f9964ab7f4720029f59e197/63354cbaab074e7d864e63b7_Current_Table_Upsert_Diagram1.png)

*Table after upsert*

![Table After Upsert](https://cdn.prod.website-files.com/5f9964ab7f4720029f59e197/63354da9b8d074128778595c_Table_After_Upsert_Diagram1.png)

In the above example, the second row's old employee details were replaced with new employee information.

### Inserting a new row [#inserting-a-new-row]

The example below shows how upsert is used to insert a new row of employee details.

*SQL statement*

```sql
UPSERT INTO employees (id, name, email) VALUES (3, 'Raj', 'Raj@testxyzHR.corp');
```

*Current table*

![Current Table](https://cdn.prod.website-files.com/5f9964ab7f4720029f59e197/63354cdf92b90d19e93f5d99_Current_Table_Upsert_Diagram2.png)

*Table after upsert*

![Table After Upsert](https://cdn.prod.website-files.com/5f9964ab7f4720029f59e197/63354d624dd271bec38feb38_Table_After_Upsert_Diagram2.png)

## Indexes, upserts, and the MERGE command [#indexes-upserts-and-the-merge-command]

The upsert is a simple function when performed on a database because the in-built indexes facilitate identification of a specific record or entry, enabling easy changes to an entry or entries. Without indexes, identifying specific records becomes difficult for operations like upsert. Although indexes have long been common in RDBMSs, they are not extensively found in data warehouses. Despite that, they are now considered essential in data warehouses because of the need to maintain a history of data with reference to the source data served into the ETL tool. A typical use case is maintaining slowly changing dimensions in a data warehouse: new records have to be inserted, data in the warehouse that is no longer in the source has to be flagged or removed, and updates made to the source must reflect in the warehouse. Upsert can help make some of these changes. However, doing the update and insert tasks separately for each instance can be tedious. Hence the MERGE command combines insert, update, and delete operations so these tasks are performed in one go without the need to write separate syntax for each. The MERGE command can be used to combine data from a source table into a target table; based on a specific condition, records can be updated, deleted, or inserted, simplifying the process of ingesting data.