A newer version of this documentation is available.

View Latest

DELETE

  • reference
February 16, 2025
+ 12

DELETE immediately removes the specified document from your keyspace.

Prerequisites

RBAC Privileges

To execute the DELETE statement, you must have the Query Delete privilege granted on the target keyspace. If the statement has any RETURNING clauses that need data read, then the Query Select privilege is also required on the keyspaces referred in the respective clauses. For more details about user roles, see Authorization.

In summary, see the below table.

Table 1.
Delete Query Contains Query Delete Permissions Needed Query Select Permissions Needed Example

WHERE clause

Yes

No

Example 1

Subquery

Yes

Yes

Example 2

RETURNING clause

Yes

Yes

Example 3

Syntax

delete ::= 'DELETE' 'FROM' target-keyspace use-keys-clause? where-clause?
            limit-clause? returning-clause?
Syntax diagram: refer to source code listing
target-keyspace

Delete Target

use-keys-clause

Delete Hint

where-clause

WHERE Clause

limit-clause

LIMIT Clause

returning-clause

RETURNING Clause

Delete Target

target-keyspace ::= keyspace-ref ( 'AS'? alias )?
Syntax diagram: refer to source code listing

Specifies the data source from which to delete the document.

keyspace-ref

Keyspace Reference

alias

AS Alias

Keyspace Reference

keyspace-ref ::= keyspace-path | keyspace-partial
Syntax diagram: refer to source code listing
keyspace-path ::= ( namespace ':' )? bucket ( '.' scope '.' collection )?
Syntax diagram: refer to source code listing
keyspace-partial ::= collection
Syntax diagram: refer to source code listing

Keyspace reference for the delete target. For more details, refer to Keyspace Reference.

AS Alias

Assigns another name to the keyspace reference. For details, refer to AS Clause.

Assigning an alias to the keyspace reference is optional. If you assign an alias to the keyspace reference, the AS keyword may be omitted.

Delete Hint

You can use a USE KEYS hint on the delete target to specify the keys of the data items to be deleted. For details, refer to USE KEYS Clause.

WHERE Clause

where-clause ::= 'WHERE' cond
Syntax diagram: refer to source code listing

Specifies the condition that needs to be met for data to be deleted. Optional.

LIMIT Clause

limit-clause ::= 'LIMIT' expr
Syntax diagram: refer to source code listing

Specifies the greatest number of objects that can be deleted. This clause must have a non-negative integer as its upper bound. Optional.

RETURNING Clause

returning-clause ::= 'RETURNING' (result-expr (',' result-expr)* |
                    ('RAW' | 'ELEMENT' | 'VALUE') expr)
Syntax diagram: refer to source code listing

Specifies the information to be returned by the operation as a query result. For more details, refer to RETURNING Clause.

Examples

Please note that running the following examples will permanently delete your sample data. To restore your sample data, remove and reinstall the travel-sample bucket. Refer to Sample Buckets for details.
Example 1. Delete query containing a WHERE clause

This example requires the Query Delete privilege on `travel-sample`.inventory.hotel.

DELETE FROM `travel-sample`.inventory.hotel;
Example 2. Delete queries containing a subquery

This example requires the Query Delete privilege on `travel-sample`.inventory.airport and the Query Select privilege on `beer-sample`.

DELETE FROM `travel-sample`.inventory.airport
WHERE city IN (SELECT raw city FROM `beer-sample` WHERE city IS NOT MISSING)
RETURNING airportname;

This example requires the Query Delete and Query Select privileges on `travel-sample`.inventory.airport.

DELETE FROM `travel-sample`.inventory.airport
WHERE city IN (SELECT RAW MAX(t.city) FROM `travel-sample`.inventory.airport AS t)
RETURNING airportname;
Example 3. Delete queries containing a RETURNING clause

These examples require the Query Delete and Query Select privileges on `travel-sample`.inventory.hotel.

DELETE FROM `travel-sample`.inventory.hotel RETURNING *;
DELETE FROM `travel-sample`.inventory.hotel
WHERE city = "San Francisco"
RETURNING meta().id;
Example 4. Delete by key

This example deletes the document airline_4444.

DELETE FROM `travel-sample`.inventory.airline k
USE KEYS "airline_4444"
RETURNING k
Results
[
  {
    "k": {
      "callsign": "MY-AIR",
      "country": "United States",
      "iata": "Z1",
      "icao": "AQZ",
      "name": "80-My Air",
      "id": "4444",
      "type": "airline"
    }
  }
]
Example 5. Delete by filter

This example deletes the airline with the callsign "AIR-X".

DELETE FROM `travel-sample`.inventory.airline f
WHERE f.callsign = "AIR-X"
RETURNING f.id
Results
[
  {
    "id": "4445"
  }
]