`UPDATE` statement

The UPDATE statement can be used to update existing records in the database. If the record does not exist, the statement will succeed but no records will be updated.

Note

This statement can not be used to create graph relationships. For that, use the RELATE or INSERT statement.

Note

UPDATE on a single record in SurrealDB 1.x will create the record if it does not exist. This behaviour is no longer the case in SurrealDB 2.0. To update and create a record if it does not exist in SurrealDB 2.0, use the UPSERT statement.

Statement syntax

SurrealQL Syntax

UPDATE [ 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 ]]
;

Note

@target refers to either record output including an id field, or a record ID on its own.

Example usage

Let's look at some examples of how to use the UPDATE statement. First we'll create two person records with the CREATE statement so that the examples will produce a meaningful output.

-- Create a Schemaless person table with a random id
CREATE person CONTENT {
    name: 'John',
    company: 'Surrealist',
    skills: ['JavaScript', 'Go' , 'SurrealQL']
};

-- Create another person with a specific id
CREATE person:tobie CONTENT {
    name: 'Tobie',
    company: 'SurrealDB',
    skills: ['JavaScript', 'Go' , 'SurrealQL']
};

Let's say we wanted to update the person table with a new field enjoys (an array), a new skill breathing to the existing skills field (another array), add a new numeric field called dollars, and a last_name field that relies on the existing name field to set its value.

To do this we would use the following query.

-- Update all records in a table
-- The `enjoys` field will also be an array.
-- The += operator alone is enough to infer the type
UPDATE person SET 
	dollars = 50,
	skills += 'breathing',
	enjoys += 'reading',
	full_name = name + ' Mc' + name + 'erson';

Output

[
	{
		company: 'Surrealist',
		dollars: 50,
		enjoys: [
			'reading'
		],
		full_name: 'John McJohnerson',
		id: person:j1qov2pxey3p8s6hqlev,
		name: 'John',
		skills: [
			'JavaScript',
			'Go',
			'SurrealQL',
			'breathing'
		]
	},
	{
		company: 'SurrealDB',
		dollars: 50,
		enjoys: [
			'reading'
		],
		full_name: 'Tobie McTobieerson',
		id: person:tobie,
		name: 'Tobie',
		skills: [
			'JavaScript',
			'Go',
			'SurrealQL',
			'breathing'
		]
	}
]

For more specific updates, you can specify a record ID to update a single record. The following query will update the record with the ID person:tobie to add "Rust" as a skill.

-- Update a record with a specific string id to add a new skill: 'Rust'
UPDATE person:tobie SET skills += 'Rust';

Output

[
	{
		company: 'SurrealDB',
		dollars: 50,
		enjoys: [
			'reading'
		],
		full_name: 'Tobie McTobieerson',
		id: person:tobie,
		name: 'Tobie',
		skills: [
			'JavaScript',
			'Go',
			'SurrealQL',
			'breathing',
			'Rust'
		]
	}
]

The -= operator can be used to remove an item from an array or reduce a numeric value by a certain value.

UPDATE person:tobie SET 
	skills -= 'Go', 
	dollars -= 1;

Output

[
	{
		company: 'SurrealDB',
		dollars: 49,
		enjoys: [
			'reading'
		],
		full_name: 'Tobie McTobieerson',
		id: person:tobie,
		name: 'Tobie',
		skills: [
			'JavaScript',
			'SurrealQL',
			'breathing',
			'Rust'
		]
	}
]

You can also remove a field from a record using the UNSET keyword or by setting the field to NONE.

-- Remove the company field by setting it to NONE or using the UNSET keyword
UPDATE person:tobie SET company = NONE;

UPDATE person:tobie UNSET company;

Output

[
	{
		dollars: 49,
		enjoys: [
			'reading'
		],
		full_name: 'Tobie McTobieerson',
		id: person:tobie,
		name: 'Tobie',
		skills: [
			'JavaScript',
			'SurrealQL',
			'breathing',
			'Rust'
		]
	}
]

Conditional Update with `WHERE` clause

The UPDATE 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 updated.

-- Update all records which match the condition that `company` is not equal to "SurrealDB"
UPDATE person SET skills += "System design" WHERE company != "SurrealDB";

Output

[
	{
		company: 'Surrealist',
		dollars: 50,
		enjoys: [
			'reading'
		],
		full_name: 'John McJohnerson',
		id: person:i5z3i64cpqpo8jtr6jww,
		name: 'John',
		skills: [
			'JavaScript',
			'Go',
			'SurrealQL',
			'breathing',
			'System design'
		]
	},
	{
		dollars: 49,
		enjoys: [
			'reading'
		],
		full_name: 'Tobie McTobieerson',
		id: person:tobie,
		name: 'Tobie',
		skills: [
			'JavaScript',
			'SurrealQL',
			'breathing',
			'Rust',
			'System design'
		]
	}
]

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.

-- Update all records with the same content
UPDATE person CONTENT {
	name: 'John',
	company: 'SurrealDB',
	skills: ['Rust', 'Go', 'JavaScript'],
};

-- Oops, now they are both named John.
-- Update a specific record with some content
UPDATE person:tobie CONTENT {
	name: 'Tobie',
	company: 'SurrealDB',
	skills: ['Rust', 'Go', 'JavaScript'],
};

Since version 2.1.0, a statement with a CONTENT clause will bypass READONLY fields instead of generating an error.

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

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

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.

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

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 CONTENT or one field at a time using SET, it is also possible to merge-update only specific fields by using the MERGE keyword followed by on object containing the fields which are to be upserted.

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

-- Update certain fields on a specific record
UPDATE person:tobie MERGE {
	settings: {
		marketing: true,
	},
};

Output

[
	{
		company: 'SurrealDB',
		id: person:i5z3i64cpqpo8jtr6jww,
		name: 'John',
		settings: {
			marketing: true
		},
		skills: [
			'Rust',
			'Go',
			'JavaScript'
		]
	},
	{
		company: 'SurrealDB',
		id: person:tobie,
		name: 'Tobie',
		settings: {
			marketing: true
		},
		skills: [
			'Rust',
			'Go',
			'JavaScript'
		]
	}
]

PATCH clause

You can also specify changes to be applied to your query response, using the PATCH command which works similar to the JSON Patch specification

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

Output

[
	{
		Engineering: 'true',
		company: 'SurrealDB',
		id: person:tobie,
		name: 'Tobie',
		settings: {
			marketing: true
		},
		skills: [
			'Rust',
			'Go',
			'JavaScript'
		]
	}
]

Alter the `RETURN` value

By default, the update statement returns the record value once the changes have been made. To change the return value of each record, use the RETURN clause, specifying NONE, BEFORE, AFTER, DIFF, a comma-separated list of specific fields or ad-hoc fields, or VALUE for a single field without its key name.

-- Don't return any result
UPDATE person SET skills += 'reading' RETURN NONE;

-- Return the changeset diff
UPDATE person SET skills += 'reading' RETURN DIFF;

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

-- Return the record after changes were applied (the default)
UPDATE person SET skills += 'reading' RETURN AFTER;

-- Return the value of the 'skills' field without the field name
UPDATE person SET skills += 'reading' RETURN VALUE skills;

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

Using a timeout

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

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

UPDATE inside database exports

As UPDATE before version 2.0.0 used to create a specified record ID if it did not exist, it was used in the .surql files generated by the surreal export command to export existing records in a database. As of version 2.0.0, the INSERT statement is used instead.

The `EXPLAIN` clause

When EXPLAIN is used:

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

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

UPDATE statement

Note

Note

Statement syntax

Note

Example usage

Conditional Update with WHERE clause

CONTENT clause

REPLACE clause

MERGE clause

PATCH clause

Alter the RETURN value

Using a timeout

UPDATE inside database exports

The EXPLAIN clause

`UPDATE` statement

Conditional Update with `WHERE` clause

Alter the `RETURN` value

The `EXPLAIN` clause