DELETE statement

The DELETE statement can be used to delete records from the database.

Statement syntax

SurrealQL Syntax
DELETE [ FROM | ONLY ] @targets
	[ WHERE @condition ]
	[ RETURN NONE | RETURN BEFORE | RETURN AFTER | RETURN DIFF | RETURN @statement_param, ... ]
	[ TIMEOUT @duration ]
	[ PARALLEL ]
	[ EXPLAIN [ FULL ]]
;

Example usage

Basic usage

The following queries shows basic usage of the DELETE statement, which is used to delete records from a table or a graph edge.

Deleting records can be done in multiple ways.

Specifying only the table name will delete all the records from a table. Note that a DELETE statement returns nothing (i.e. an empty array) by default.

-- Delete all records from a table
DELETE person;

Output
[]

A DELETE statement on a specific ID will delete a single record.

-- Delete a record with a specific numeric id
DELETE person:100;

-- Delete a record with a specific string id
DELETE person:tobie;

The ONLY keyword can be followed by a RETURN BEFORE clause to return just the object for the record in question before it was deleted.

DELETE ONLY person:tobie RETURN BEFORE;

Note that as a DELETE statement returns an empty array by default, and the ONLY keyword assumes that a single object will be returned, it will return an error if RETURN BEFORE is not included.

DELETE ONLY person:tobie;

Output
'Expected a single result output when using the ONLY keyword'

Deleting records based on conditions

The delete 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 deleted.

-- Update all records which match the condition
DELETE city WHERE name = 'London';

By default, the delete statement does not return any data, returning only an empty array if the statement succeeds completely. Specify a RETURN clause to change the value which is returned for each document that is deleted.

-- Don't return any result (the default)
DELETE user WHERE age < 18 RETURN NONE;

-- Return the changeset diff
DELETE user WHERE interests CONTAINS 'reading' RETURN DIFF;

-- Return the record before changes were applied
DELETE user WHERE interests CONTAINS 'reading' RETURN BEFORE;

-- Return the record after changes were applied
DELETE user WHERE interests CONTAINS 'reading' RETURN AFTER;

An important point to know when using a WHERE clause is that it performs a check on the truthiness of a value, namely whether a value exists and is not a default value like 0, an empty string, empty array, and so on.

As such, the DELETE query below that only specifies WHERE age essentially evaluates to “WHERE age exists” and will delete every cat in the database with an age.

CREATE cat:one SET age = 4;
CREATE cat:two;
DELETE cat WHERE age;
SELECT * FROM cat;

Output
[
	{
		id: cat:two
	}
]

This pattern is particularly useful when using SurrealDB’s literal types which were added in version 2.0. A literal type containing objects that contain a single top-level field can easily be matched on through the field name.

DEFINE FIELD error_info ON TABLE info TYPE
      { continue: { message: "Continue" } }
    | { retry_with_id: { error: string  } }
    | { deprecated: { message: string   } };

CREATE info SET error_info = { continue: { message: "Continue" }};
CREATE info SET error_info = { continue: { message: "Continue" }};
CREATE info SET error_info = { deprecated: { message: "We don't use this anymore" }};

DELETE info WHERE error_info.continue;
SELECT * FROM info;

Output
[
	{
		error_info: {
			deprecated: {
				message: "We don't use this anymore"
			}
		},
		id: info:o0pmm7zos98iv03xliav
	}
]

Using TIMEOUT duration records based on conditions

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 deleted from the database, and the statement will return an error.

DELETE person WHERE ->knows->person->(knows WHERE influencer = false) TIMEOUT 5s;

Deleting graph edges

You can also delete graph edges between two records in the database by using the DELETE statement.

For example the graph edge below:

RELATE person:tobie->bought->product:iphone;

[
	{
		"id": bought:ctwsll49k37a7rmqz9rr,
		"in": person:tobie,
		"out": product:iphone
	}
]

Can be deleted by:

DELETE person:tobie->bought WHERE out=product:iphone;

Soft deletions

While soft deletions do not exist natively in SurrealDB, they can be simulated by defining an event that reacts whenever a deletion occurs.

The following example archives the data of a deleted record in another table. This can be combined with fewer permissions for the new table so that it can be accessed only by system users and not record users.

DEFINE EVENT archive_person ON TABLE person WHEN $event = "DELETE" THEN {
    CREATE deleted_person SET
        data = $before,
        deleted_at = time::now()
};

CREATE |person:1..3|;
DELETE person:1;

-- Only two `person` records left
SELECT * FROM person;
-- But the data of `person:1` is still here
SELECT * FROM deleted_person;

Output
-------- Query --------

[
	{
		id: person:2
	},
	{
		id: person:3
	}
]

-------- Query --------

[
	{
		data: {
			id: person:1
		},
		deleted_at: d'2024-09-12T00:46:59.176Z',
		id: deleted_person:p3fpzhpxuu9jvjn8juyf
	}
]

The EXPLAIN clause

When EXPLAIN is used:

1. The DELETE statement returns an explanation, essentially revealing the execution plan to provide transparency and understanding of the query performance.
2. The records are not deleted.

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