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.

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

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

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

[
	{
		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;

[
	{
		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;

[
	{
		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";

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

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 always held for ordinary fields - SET a = a + 1, b = a + 1 assigns b from the old a. 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.

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

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

[
	{
		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 similarly to the JSON Patch specification.

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

[
	{
		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:

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

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

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.

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.

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:

1. 1.

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

2. 2.

   The records are not updated.

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