UPSERT statement

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

This is different from the UPDATE statement, which will fail if the record does not exist.

Statement syntax

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

Example usage

For example, if you want to UPSERT a record if it exists, or create it if it does not, you can use the UPSERT statement.

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

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

In the case where the record ID isn't specified, any exisiting records in the table will be UPSERTd. For example, the following query will UPSERT all records in the person table:

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

The UPSERT statement supports conditional matching using the WHERE clause. For example, the following query will upsert the record with the specified ID only if the name field is equal to 'Tobie':

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

Using ONLY clause

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

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

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

Using WHERE clause

The UPSERT statement supports conditional matching of records using a WHERE clause. If the expression in the WHERE clause evaluates to true, then the respective record will be UPSERTd.

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

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

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

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

What's on your mind?

Create GitHub Issue

Quickest way to alert
our team of a problem.