UPSERT statement

Available since: v2.0.0

The UPSERT statement can be used to modify records in the database if they already exist. If a specified record ID does not exist, it will be created.

This is different from the UPDATE statement, which will do nothing if a specified record ID does not exist.

Statement syntax

SurrealQL Syntax
UPSERT [ ONLY ] @targets
    [ CONTENT @value
      | MERGE @value
      | PATCH @value
      | [ SET @field = @value, ... | UNSET @field, ... ]
    ]
    [ WHERE @condition ]
    [ RETURN NONE | RETURN BEFORE | RETURN AFTER | RETURN DIFF | RETURN @statement_param, ... ]
    [ TIMEOUT @duration ]
    [ PARALLEL ]
;

Example usage

If you want to update a record if it exists, or create it if it does not, you can use the UPSERT statement with a specific record ID.

-- Update or create a record with a specific numeric id
UPSERT person:100 SET 
	name = 'Tobie', 
	company = 'SurrealDB', 
	skills = ['Rust', 'Go', 'JavaScript'];

In the case where the record ID isn’t specified, any existing records in the table will be updated. For example, the following query will update all records in the person table but will not create any new ones.

-- UPSERT all records in a table
-- The skills field is an array. The += operator alone is enough for SurrealDB to infer the type
UPSERT person SET skills -= 'breathing';

Using ONLY clause

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

-- 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>.

-- 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.

-- 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.

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

Possible output
[
	{
		id: event_for:[
			'2024-09-18'
		],
		number: 1
	}
]

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

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;
};

Using WHERE clause

The UPSERT statement supports conditional matching using the WHERE clause, effectively turning it into an UPDATE statement. For example, the following query will update the record with the specified ID only if the name field is equal to ‘Tobie’ and will not create it otherwise.

-- UPSERT a record with a specific numeric id only if the name is 'Tobie'
UPSERT person:100 SET 
	name = 'Tobie', 
	company = 'SurrealDB', 
	skills = ['Rust', 'Go', 'JavaScript']
WHERE name = 'Tobie';

-- UPSERT all records which match the condition
UPSERT city SET population = 9541000 WHERE name = 'London';

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.

-- 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'],
};

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.

-- UPSERT certain fields on all records
UPSERT person MERGE {
	settings: {
		marketing: true,
	},
};

-- UPSERT 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

-- 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.

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

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

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

-- Return the record after changes were applied (the default)
UPSERT person 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.

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