UPDATE

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. To compare it with the other statements that write records, see Writing records that may already exist.

Note

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

Note

UPDATE does not create records that do not exist. To update a record and create it if it does not exist, 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

The examples below show how to use the UPDATE statement. They start by creating two person records with the CREATE statement so that the examples produce meaningful output.

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

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

Suppose we want 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: 'SurrealDB Studio',
		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'
		]
	}
]

To rename a field on every record of a table, copy it to the new name and set the old name to NONE in the same statement. Every expression in SET reads the record as it was before the statement, so username = name still reads the old value even though name is removed in the same statement:

CREATE user:1 SET name = 'Ann';
CREATE user:2 SET name = 'Bob';

UPDATE user SET username = name, name = NONE;

Output

[
	{ id: user:1, username: 'Ann' },
	{ id: user:2, username: 'Bob' }
]

On a schemafull table, define the new field and remove the definition of the old one before the UPDATE. While the old field is still defined with a type such as string, setting it to NONE fails. The stored values stay readable after the definition is removed, so the UPDATE can still copy them.

Updating objects inside an array

Setting a field through an array path, such as partnerships.pl, sets the same value on every object in the array. The value is calculated once for the whole record, so a path that reads the array in the value gives an array, not the value of the current element:

CREATE deal:one SET amount = 400, partnerships = [
	{ share: 0.5, pl: 0 },
	{ share: 0.25, pl: 0 }
];

UPDATE deal:one SET partnerships.pl = amount * partnerships.share;
//- "Cannot perform multiplication with 'int' and 'array'"

To calculate a value for each element, replace the array with a .map() over it. The closure can read the other fields of the record, such as amount, and + on two objects keeps the fields of the first and adds or replaces the fields of the second:

UPDATE deal:one SET partnerships = partnerships.map(|$p| $p + { pl: amount * $p.share });

Output

[
	{
		amount: 400,
		id: deal:one,
		partnerships: [
			{ pl: 200f, share: 0.5f },
			{ pl: 100f, share: 0.25f }
		]
	}
]

To change only some elements, filter the path with [WHERE] or pick one by its index:

UPDATE deal:one SET partnerships[WHERE share > 0.3].pl = 0;
UPDATE deal:one SET partnerships[1].pl = 0;

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: 'SurrealDB Studio',
		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'
		]
	}
]

Evaluation order

Available since: v3.3.0

The WHERE condition is checked before the data clause is evaluated, so a data clause with side effects only runs for the records the condition matches. The same applies to UPSERT when it updates an existing record.

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

-- No record matches, so the query effectively becomes this:
UPDATE person /* 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.

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.

One image of the record

Available since: v3.3.0

The WHERE condition and the data clause read the same image of the record, taken before the statement changes anything. Reads therefore never observe the statement's own writes, and both clauses agree on what the record contains.

This has held for ordinary fields since 3.0.0, so SET a = a + 1, b = a + 1 assigns b from the old a. Before 3.0.0 the assignments ran in the order they were written, and b read the new a (see the 2.x to 3.x upgrade notes). Computed fields are now part of that image too, so a data clause and a condition both see their pre-mutation values.

DEFINE FIELD can_drive ON person COMPUTED age >= 18;
CREATE person:1 SET age = 17;

-- `can_drive` is false in all three: it reflects age 17, not the new age of 18
UPDATE person:1 SET age = 18, my_field = can_drive;
UPDATE person:1 SET my_field = can_drive, age = 18;
UPDATE person:1 SET my_field = can_drive, age = 18 WHERE can_drive = false;

A statement that creates a record has no earlier image, so a computed field reads NONE there - whether the record ID was named, generated, or reached through UPSERT.

Note

Before 3.3.0, a computed field read NONE in the data clause but held its value in the WHERE condition, so whichever clause ran first decided what the other saw. The first two statements above returned NONE and the third returned false.

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

A statement with a CONTENT clause bypasses 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 --------
[
	{
		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 an 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'
		]
	}
]

MERGE combines objects at every level, so a nested object in the merge keeps the fields it does not mention. An array is a single value rather than a set of fields, so an array in the merge replaces the stored array whole, including an array of objects:

CREATE doc:1 SET tags = ['a', 'b'], meta = { x: 1, y: 2 };

UPDATE doc:1 MERGE { meta: { y: 3 }, tags: ['c'] };

Output

[
	{
		id: doc:1,
		meta: { x: 1, y: 3 },
		tags: ['c']
	}
]

To add to an array or remove from it instead of replacing it, use SET tags += 'd' or SET tags -= 'a'.

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

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;

Counting the records a statement changed

UPDATE returns one entry for each record it changed, so the length of its output is the number of records changed. The same works for DELETE with RETURN BEFORE, since a DELETE returns nothing by default:

CREATE |item:1..=5| SET old = true;

(UPDATE item SET checked = true WHERE old).len();
//- 5

(DELETE item WHERE old RETURN BEFORE).len();
//- 5

(DELETE item WHERE old RETURN BEFORE).len();
//- 0

For an UPDATE, add RETURN VALUE id to keep the output small when only the count is needed. For a DELETE, keep RETURN BEFORE: the deleted records no longer have an id, so RETURN VALUE id returns NONE for each of them.

PATCH clause

You can also specify changes to be applied to your query response, using the PATCH command, which works similarly 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'
		]
	}
]

Operations

A patch is an array of operations, each naming its target with a JSON Pointer - a /-separated path into the record, so /settings/marketing reaches the marketing field inside settings. SurrealDB accepts the six operations of the JSON Patch specification plus change:

Operation	Takes	Does
`add`	`path`, `value`	Sets the member at `path` on an object, or inserts at that position in an array
`remove`	`path`	Deletes the field at `path`
`replace`	`path`, `value`	Overwrites the field at `path`
`copy`	`from`, `path`	Reads the value at `from` and writes it to `path`
`move`	`from`, `path`	Reads the value at `from`, writes it to `path`, and deletes `from`
`test`	`path`, `value`	Fails the whole patch unless the field at `path` already equals `value`
`change`	`path`, `value`	Applies a text diff to the string at `path`

An unknown operation fails the statement with The JSON Patch contains invalid operations, and so does a test whose value does not match, which is what makes test useful as a guard in front of the operations that follow it.

CREATE person:tobie SET name = 'Tobie', company = 'SurrealDB', nickname = 'Tobie';

UPDATE person:tobie PATCH [
	{ "op": "replace", "path": "/company", "value": "SurrealDB Ltd" },
	{ "op": "copy", "from": "/name", "path": "/display_name" },
	{ "op": "remove", "path": "/nickname" }
];

Output

[
	{
		company: 'SurrealDB Ltd',
		display_name: 'Tobie',
		id: person:tobie,
		name: 'Tobie'
	}
]

The value of a change operation is a text patch in the format of the diff-match-patch library. Each hunk starts with an @@ header giving character positions, followed by lines that start with a space for unchanged text, - for removed text and + for added text.

CREATE person:tobie SET company = "SurrealDB";

UPDATE person:tobie PATCH [
	{ "op": "change", "path": "/company", "value": "@@ -2,8 +2,12 @@\n urrealDB\n+ Ltd\n" }
];

Output

[
	{
		company: 'SurrealDB Ltd',
		id: person:tobie
	}
]

value::diff() produces this format, so UPDATE person:tobie PATCH value::diff({ company: 'SurrealDB' }, { company: 'SurrealDB Ltd' }) applies the same change. A diff whose context does not match the current string leaves the field unchanged, and the statement does not report an error.

Pointers into arrays

Available since: v3.3.0

A segment addresses a position in an array only in the canonical spelling RFC 6901 gives it, so a member named 007, +3 or -1 stays addressable as a member rather than being read as an index. The segment - addresses the position after the last element.

add, and the destination of copy and move, insert at the position and shift the elements after it, rather than overwriting the element already there. move removes its source before writing its destination, so a destination in the same array counts against the array that removal left behind.

replace needs an element to exist at the position it names and fails otherwise, matching the bounds add already enforced. On an object it still creates a missing member.

Two pointers are refused rather than guessed at: one whose prefix reaches every element of a sequence, since it names no single position, and a destination inside its own source, which would otherwise nest a copy of the source inside itself each time it ran.

Writing to a set by position reaches whichever element sorts into that place, so a set is rebuilt once the patch has been applied.

Warning

Before 3.3.0 a pointer through an array index did not apply at all, and replace corrupted the record on top of that, giving every element of the array a member named after the index segment. Replacing the whole array was the only reliable way to change one property of one element.

Read pointers and field permissions

Three PATCH operations read from a pointer as well as writing: copy and move read the path in their from, and test reads its path. Each of those reads resolves against the caller's permitted view of the record, and the statement fails with a not-allowed error where the pointer reaches a field the caller cannot select.

This holds for a pointer at the restricted field itself, at an ancestor object that holds it, and at a nested leaf beneath it.

The check walks the operations in order against a copy of the record, judging each pointer as the record stands when that operation runs. A patch is a sequence, so an earlier insertion or removal shifts every later element by one, and a pointer that reached nothing when the patch arrived can come to reach a restricted field by the time its own operation runs.

add and change also read a pointer, but each writes its result back to the path it read, so neither moves a value out of the field it came from and neither is gated.

The other data clauses reduce rather than refuse: a SET, CONTENT, MERGE or REPLACE right-hand side sees NONE where a field is restricted. PATCH fails the statement instead, because substituting NONE would store a value the caller never asked for, and for move would delete the source field as well. The failure reveals that a field is restricted, which a plain SELECT already shows by omitting it, rather than the value.

Every statement that takes a PATCH clause behaves this way, so the same applies to CREATE, UPSERT, RELATE and INSERT.

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;

The `EXPLAIN` clause

When EXPLAIN is used:

The UPDATE statement returns an explanation of its execution plan, which shows how the query will be performed.
The records are not updated.

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

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.

Statement syntax

Example usage

Updating objects inside an array

Conditional update with WHERE clause

Evaluation order

One image of the record

CONTENT clause

MERGE clause

REPLACE clause

Alter the RETURN value

Counting the records a statement changed

PATCH clause

Operations

Pointers into arrays

Read pointers and field permissions

Using a timeout

The EXPLAIN clause

UPDATE inside database exports

Conditional update with `WHERE` clause

Alter the `RETURN` value

The `EXPLAIN` clause