UPDATE statement

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

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

Statement syntax

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

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

Example usage

The following query shows example usage of this statement.

NOTE : In the case where the record ID doesn't exist, the statement will fail and no records will be updated. Be sure to create the record using the CREATE statement before updating it. Alternatively, you can use the UPSERT statement to create the record if it doesn't exist.

Let's look at some examples of how to use the UPDATE statement: First we'll create a person table with the CREATE Statement so that the examples can be run.

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

Let's say we wanted to update the person table with a new field skills and add a new skill breathing to the existing skills.

To do this we would use the following query:

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

The above query will update all records in the person table by adding the skill breathing to the skills field.

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

CREATE person:tobie CONTENT {
    name: 'Tobie',
    company: 'SurrealDB',
    skills: ['JavaScript', 'Go' , 'SurrealQL']
};

Next, you can update the record with the ID person:tobie to add "Rust" as a skill, using the following query:

-- Update a record with a specific string id to add a new skill: 'Rust'
UPDATE person:tobie SET skills = ['JavaScript', 'Go' , 'SurrealQL','Rust']; 

Note: When specifying fields to update using the SET clause, it is possible to increment and decrement numeric values, and add or remove values from arrays. To increment a numeric value, or to add an item to an array, use the += operator. To decrement a numeric value, or to remove a value from an array, use the -= operator.

-- Update a document and remove a tag from skills array
UPDATE person:tobie SET skills -= 'Go';

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;

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
UPDATE person SET skills += "System design" WHERE company = "SurrealDB";

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: 'Tobie',
	company: 'SurrealDB',
	skills: ['Rust', 'Go', 'JavaScript'],
};

-- Update a specific record with some content
UPDATE 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-update only specific fields by using the MERGE keyword and specifying only the fields which are to be updated.

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

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

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, 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
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 a specific field only from the updated records
UPDATE person:tobie SET skills = ['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 updated in the database, and the statement will return an error.

UPDATE person:tobie 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.