# UPSERT

The UPSERT statement can be used to insert records or modify records that already exist.

The `UPSERT` statement can be used to insert records into the database, or to update them if they exist.

> [!NOTE]
> In versions of SurrealDB between 2.0.0 and 2.0.4, an UPSERT statement was treated as an "UPDATE, otherwise INSERT" statement. It has since been changed to a statement which defaults to insertion, and updates otherwise. Please see the examples below for details.

### Statement syntax

**SurrealQL Syntax**

```syntax title="SurrealQL Syntax"
UPSERT [ ONLY ] @targets
    [ CONTENT @value
      | MERGE @value
      | PATCH @value
	  | REPLACE @value
      | [ SET @field = @value, ... | UNSET @field, ... ]
    ]
    [ WHERE @condition ]
    [ RETURN NONE | RETURN BEFORE | RETURN AFTER | RETURN DIFF | RETURN @statement_param, ... | RETURN VALUE @statement_param ]
    [ TIMEOUT @duration ]
	[ EXPLAIN [ FULL ]]
;
```

## Example usage

Conceptually, an `UPSERT` statement can be thought of as an "`INSERT`, otherwise `UPDATE`" statement.

### `UPSERT` without a `WHERE` clause

As an `UPSERT` statement is primarily an `INSERT` statement, one without a `WHERE` clause will not perform an update.

```surql
UPSERT person SET name = 'Billy';
UPSERT person SET name = 'Bobby';
SELECT * FROM person;
```

As the output shows, the second `UPSERT` simply inserted another `person` record with the name "Bobby", rather than updating the existing record.

```surql title="Output"
-------- Query --------
[
	{
		id: person:c2bl54ahi551fcx9dqri,
		name: 'Billy'
	}
]

-------- Query --------
[
	{
		id: person:886dcoe1ayul217nl2fu,
		name: 'Bobby'
	}
]

-------- Query --------
[
	{
		id: person:886dcoe1ayul217nl2fu,
		name: 'Bobby'
	},
	{
		id: person:c2bl54ahi551fcx9dqri,
		name: 'Billy'
	}
]
```

### Using the `WHERE` clause

#### Without a specified ID

When using the `WHERE` clause and no specified ID, SurrealDB will check to see if any records match the clause. If nothing matches, a new record will be created.

As such, the following `UPSERT` statement will return a new record:

```surql
UPSERT person SET name = 'Jaime' WHERE name = 'Jaime';
```

```surql title="Output"
[
	{
		id: person:7ilunylkcjgbg9gf0tqn,
		name: 'Jaime'
	}
]
```

Since a record with the name 'Jaime' exists, an `UPSERT` followed by `WHERE name = 'Jaime'` will update the existing record instead of creating a new one.

```surql
UPSERT person SET name = 'Tobie' WHERE name = 'Jaime';
```

```surql title="Output"
[
	{
		id: person:7ilunylkcjgbg9gf0tqn,
		name: 'Tobie'
	}
]
```

Now that no records have the name `'Jaime'`, the same query as above will now create a new record because no records match the `WHERE` clause. The database will now have two `person` records.

```surql
UPSERT person SET name = 'Tobie' WHERE name = 'Jaime';
SELECT * FROM person;
```

```surql title="Output"
-- Query
[
	{
		id: person:0n0ddlkmhe6mdb6ikkui,
		name: 'Tobie'
	}
]

-- Query
[
	{
		id: person:0n0ddlkmhe6mdb6ikkui,
		name: 'Tobie'
	},
	{
		id: person:7ilunylkcjgbg9gf0tqn,
		name: 'Tobie'
	}
]
```

#### With a specified ID

`UPSERT` behaviour with a specific ID and a `WHERE` clause differs slightly from the examples above. In this case, there is the possibility that a record ID already exists but the `WHERE` clause does not match. As such, there is no way to create a new record as the statement only pertains to an ID for an already existing record.

The following query will return a record, because the `person:test` record does not yet exist. The `WHERE` clause makes no difference as there is no record to apply it to.

```surql
UPSERT person:test SET name = 'Jaime' WHERE name = 'Jaime';
```

```surql title="Output"
[
	{
		id: person:test,
		name: 'Jaime'
	}
]
```

The following query will update the `person:test` record, because the record exists and the `WHERE` clause matches. The `person:test` record will now have the name `'Tobie'`.

```surql
UPSERT person:test SET name = 'Tobie' WHERE name = 'Jaime';
```

```surql title="Output"
[
	{
		id: person:test,
		name: 'Tobie'
	}
]
```

However, this third query will return nothing. The `WHERE` clause does not match and thus `person:test` cannot be updated, and the statement itself only pertains to the `person:test` record, so a new record using a random ID will not be returned.

```surql
UPSERT person:test SET name = 'Billy' WHERE name = 'Jaime';
```

```surql title="Output"
[]
```

### Evaluation order

_(since v3.3.0)_

When an `UPSERT` updates an existing record, the `WHERE` condition is checked before the data clause is evaluated. A data clause with side effects therefore only runs for the records the condition matches, matching [`UPDATE`](/docs/reference/query-language/statements/update.md#evaluation-order).

```surql
CREATE person:jaime SET name = 'Jaime';

-- Updates nothing, and creates no `audit` record
UPSERT person:jaime SET last_audit = (CREATE ONLY audit SET at = time::now()).id WHERE false;

-- `person:jaime` does not match, so the query effectively becomes this:
UPSERT person:jaime /* SET last_audit = (CREATE ONLY audit SET at = time::now()).id */ WHERE false;
```

Validation of the data clause is deferred in the same way, so a data clause that would be rejected for a record no longer raises an error when the condition excludes that record.

The condition only gates the update path. When an `UPSERT` creates a record instead, the `WHERE` clause is not evaluated at all - as described in [With a specified ID](#with-a-specified-id) above - and the data clause runs.

```surql
-- `person:billy` does not exist, so it is created and an `audit` record is written
UPSERT person:billy SET last_audit = (CREATE ONLY audit SET at = time::now()).id WHERE false;
```

> [!NOTE]
> Before SurrealDB 3.3.0, the data clause was evaluated for every scanned record on a full table scan, so its side effects fired for records the condition rejected. On an index-backed plan they did not, which meant that adding an index changed how many times the side effects ran.

### Improved performance via UPSERT and a unique index

[Unique indexes](/docs/reference/query-language/statements/define/indexes.md#unique-index) can be used to ensure that no field or combination of fields is ever present more than once. For example, a game might have a rule that duplicate names can exist, but not within the same class.

```surql
DEFINE INDEX unique_key ON TABLE user FIELDS name, class UNIQUE;
DEFINE FIELD official_name ON TABLE user VALUE name + " the " + class;
CREATE user:billy SET name = "Billy",
  class = "wizard",
  metadata = { likes: ["strawberries"] };
CREATE user:billy_der_zweite SET name = "Billy",
  class = "wizard",
  metadata = { likes: ["strawberries",
  "fields"] };
```

As the output shows, the unique index prevents the creation of a second user with the name and class as the first.

```surql title="Output"
-------- Query --------
[
	{
		id: user:billy,
		metadata: {
			likes: [
				'strawberries'
			]
		},
		name: 'Billy',
		official_name: 'Billy the wizard',
		class: 'wizard'
	}
]

-------- Query --------
"Database index `unique_key` already contains ['Billy',
  'wizard'],
  with record `user:billy`"
```

To change an existing record's `metadata` field to the value `{ likes: ["strawberries", "fields"] }`, an `UPDATE` with a `WHERE` can be used. This performs a scan on the `user` table to check for all records that match the `WHERE` clause.

```surql
UPDATE user SET
	metadata = { likes: ["strawberries", "fields"] }
WHERE
	name = "Billy" AND
	class = "wizard";
```

However, a much more efficient method is available if you only need to update one record and have a unique index that can be used instead. This optimisation is available when using `UPSERT` and a unique index, because the statement will always access the index in any case to first see if the record is a duplicate.

```surql
-- Checks the index for ["Mandy", "wizard"], no existing
-- record found so no problem
UPSERT user SET name = "Mandy", class = "wizard";

-- Fails because statement tries to upsert a new user:mandy
-- on top of the previous randomly generated one
UPSERT user:mandy SET 
	name = "Mandy",
	class = "wizard",
	metadata = { likes: ["strawberries" ]};
```

```surql title="Output"
-------- Query --------

[
	{
		class: 'wizard',
		id: user:kdvh401gofckvvy6nbiw,
		name: 'Mandy',
		official_name: 'Mandy the wizard'
	}
]

-------- Query --------

"Database index `unique_key` already contains ['Mandy',
  'wizard'],
  with record `user:kdvh401gofckvvy6nbiw`"
```

Since an `UPSERT` statement already checks unique indexes by default, it uses this to recognize that the user with `name = "Billy"` and `the = "Wizard"` corresponds to the record `user:j2ecdb2tf4ou29mr0yp5` and update it without needing to scan the entire `user` table.

```surql
UPSERT user SET
	name = "Billy",
	class = "wizard",
	metadata = { likes: ["strawberries", "fields"] };
```

```surql title="Output"
-------- Query --------
[
	{
		id: user:j2ecdb2tf4ou29mr0yp5,
		metadata: {
			likes: [
				'strawberries'
			]
		},
		name: 'Billy',
		official_name: 'Billy the wizard',
		class: 'wizard'
	}
]
```

To compare the performance difference between using a `WHERE` clause and a unique index yourself, here is an example that creates a crowded field of 50000 `user` records, followed by one more `user` named "Billy". An `UPDATE` using `WHERE name = "Billy" AND class = "wizard"` requires a full table scan, while an `UPSERT` using the two fields used to build the index is much faster.

```surql
DEFINE INDEX unique_key ON TABLE user FIELDS name, class UNIQUE;
DEFINE FIELD official_name ON TABLE user VALUE name + " the " + class;

-- Add 50000 users to fill up the database
FOR $i IN <array>0..50000 { CREATE user SET name = <string>$i,
  class = <string>$i;
}

-- Create Billy, one of 50,001 records
CREATE user SET name = "Billy", class = "wizard";

-- Updating Billy requires a table scan
UPDATE user SET
	interests += "music"
WHERE
	name = "Billy" AND
	class = "wizard";

-- But UPSERT uses 'name' and 'class' to check the index anyway,
-- and thus can use it to access the record without a scan
UPSERT user SET
	name = "Billy",
	class = "wizard",
	interests += "travel";
```

## Using the ONLY clause

The `ONLY` clause can be used to return a single record instead of an array of records.

```surql
-- UPSERT just a single record
-- Using the ONLY keyword, just an object for the record in question will be returned.
-- This, instead of an array with a single object.
UPSERT ONLY person:tobie SET 
	name = 'Tobie', 
	company = 'SurrealDB', 
	skills = ['Rust', 'Go', 'JavaScript'];
```

## Type inference when using UPSERT

The `+=` operator in the following query is enough for SurrealDB to infer that the `interests` field must be an `array<string>`.

```surql
-- UPSERT a document and remove a tag from an array
UPSERT person:tobie SET interests += 'Java';
```

Type inference will also work with a numeric value such as the `click_count` field below, in which case it will infer the field to be of type `int` with a default value of 0.

```surql
-- UPSERT a document and increment a numeric value
UPSERT webpage:home SET click_count += 1;
```

Creating a record by default makes the `UPSERT` statement an ideal way to manage an incrementing field.

```surql
UPSERT event_for:[time::now().format("%Y-%m-%d")] SET
    number += 1;
```

```surql title="Possible output"
[
	{
		id: event_for:[
			'2024-09-18'
		],
		number: 1
	}
]
```

Doing the same with an `UPDATE` statement would require much more manual work.

```surql
IF (SELECT *
  FROM event_for:[time::now().format("%Y-%m-%d")]).is_empty() {
    CREATE event_for:[time::now().format("%Y-%m-%d")] SET number = 1;
} ELSE {
    UPDATE event_for:[time::now().format("%Y-%m-%d")] SET number += 1;
};
```

## CONTENT clause

Instead of specifying record data using the `SET` clause, it is also possible to use the `CONTENT` keyword to specify the record data using a SurrealQL object.

```surql
-- UPSERT all records with the same content
UPSERT person CONTENT {
	name: 'Tobie',
	company: 'SurrealDB',
	skills: ['Rust', 'Go', 'JavaScript'],
};

-- UPSERT a specific record with some content
UPSERT person:tobie CONTENT {
	name: 'Tobie',
	company: 'SurrealDB',
	skills: ['Rust', 'Go', 'JavaScript'],
};
```

A statement with a `CONTENT` clause bypasses `READONLY` fields instead of generating an error.

```surql
DEFINE FIELD created
  ON person TYPE datetime DEFAULT d'2024-01-01T00:00:00Z' READONLY;
UPSERT person:gladys SET age = 90;
-- Does not try to modify `created` field, no error
UPSERT person:gladys CONTENT { age: 70 };
```

**Output before 2.1.0**

```surql
-------- Query --------
[
	{
		age: 90,
		created: d'2024-01-01T00:00:00Z',
		id: person:gladys
	}
]

-------- Query --------
'Found changed value for field `created`,
  with record `person:gladys`,
  but field is readonly'
```

**Output after 2.1.0**

```surql
-------- Query --------
[
	{
		age: 90,
		created: d'2024-01-01T00:00:00Z',
		id: person:gladys
	}
]

-------- Query --------
[
	{
		age: 70,
		created: d'2024-01-01T00:00:00Z',
		id: person:gladys
	}
]
```

## REPLACE clause

Originally an alias for `CONTENT`, the `REPLACE` clause maintains the previous behaviour regarding `READONLY` fields. If the content following `REPLACE` does not match a record's `READONLY` fields, an error will be generated.

```surql
DEFINE FIELD created
  ON person TYPE datetime DEFAULT d'2024-01-01T00:00:00Z' READONLY;
UPSERT person:gladys SET age = 90;
-- Attempts to change `created` field, error
UPSERT person:gladys REPLACE { age: 70 };
-- `created` equals current value, query works
UPSERT person:gladys REPLACE { age: 70,
  created: d'2024-01-01T00:00:00Z' };
```

```surql title="Output"
-------- Query --------
[
	{
		age: 90,
		created: d'2024-01-01T00:00:00Z',
		id: person:gladys
	}
]

-------- Query --------
'Found changed value for field `created`,
  with record `person:gladys`,
  but field is readonly'

-------- Query --------
[
	{
		age: 70,
		created: d'2024-01-01T00:00:00Z',
		id: person:gladys
	}
]
```

## MERGE clause

Instead of specifying the full record data using the `SET` clause or the `CONTENT` keyword, it is also possible to merge-UPSERT only specific fields by using the `MERGE` keyword and specifying only the fields which are to be upserted.

```surql
-- Inserts a new record with a single field and random ID
UPSERT person MERGE {
	settings: {
		marketing: true,
	},
};

-- Updates certain fields on a specific record
UPSERT person:tobie MERGE {
	settings: {
		marketing: true,
	},
};
```

## PATCH clause

You can also specify changes to be applied to your query response, using the `PATCH` clause which works similar to the [JSON Patch specification](https://jsonpatch.com/)

```surql
-- Patch the JSON response
UPSERT person:tobie PATCH [
	{
		"op": "add",
		"path": "Engineering",
		"value": "true"
	}
];
```

## Alter the `RETURN` value

By default, the UPSERT statement returns the record value once the changes have been made. To change the return value of each record, specify a `RETURN` clause, specifying either `NONE`, `BEFORE`, `AFTER`, `DIFF`, or a comma-separated list of specific fields to return.

```surql
-- Don't return any result
UPSERT person:tobie SET interests += 'reading' RETURN NONE;

-- Return the changeset diff
UPSERT person:tobie SET interests += 'reading' RETURN DIFF;

-- Return the record before changes were applied
UPSERT person:tobie SET interests += 'reading' RETURN BEFORE;

-- Return the record after changes were applied (the default)
UPSERT person:tobie SET interests += 'reading' RETURN AFTER;

-- Return a specific field only from the upserted records
UPSERT person:tobie SET interests = ['skiing',
  'music'] RETURN name,
  interests;
```

When processing a large result set with many interconnected records, it is possible to use the `TIMEOUT` keywords to specify a timeout duration for the statement. If the statement continues beyond this duration, then the transaction will fail, no records will be upserted in the database, and the statement will return an error.

```surql
UPSERT person:3 SET important = true
  WHERE ->knows->person->(knows
  WHERE influencer = true) TIMEOUT 5s;
```

## The `EXPLAIN` clause

When `EXPLAIN` is used:

1. The `UPSERT` statement returns an explanation, essentially revealing the execution plan to provide transparency and understanding of the query performance.
2. The records are not updated.

`EXPLAIN` can be followed by `FULL` to see the number of executed rows.