DELETE

  • Capella Operational
  • reference
    +

    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 Configure Cluster Access Credentials.

    RBAC Examples
    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? offset-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

    offset-clause

    OFFSET 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.

    OFFSET Clause

    offset-clause ::= 'OFFSET' expr
    Syntax diagram: refer to source code listing

    Like the OFFSET clause for a SELECT query, you can include an OFFSET clause in a DELETE statement to specify a number of objects to skip before beginning the deletion. This option can be useful for parallelizing a large delete operation.

    You can include the OFFSET clause either before or after the optional LIMIT clause. The position has no effect on the result.

    The expression for this clause must be a non-negative integer. 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

    To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

    Be aware 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 Import Sample Data for details.
    Example 1. Delete query containing a WHERE clause

    This example requires the Query Delete privilege on hotel.

    DELETE FROM hotel;
    Example 2. Delete queries containing a subquery

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

    DELETE FROM 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 airport.

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

    These examples require the Query Delete and Query Select privileges on hotel.

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

    This example deletes the document airline_4444.

    DELETE FROM 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 airline f
    WHERE f.callsign = "AIR-X"
    RETURNING f.id
    Results
    [
      {
        "id": "4445"
      }
    ]
    Example 6. Delete with LIMIT and OFFSET

    This example deletes a subset of the airlines with a country of "France'. First, you query to get a list of the airlines in France.

    SELECT id FROM airline 
    WHERE country="France";

    There are 21 documents in this collection with country="France".

    Results
    [
        {
          "id": 1191
        },
        {
          "id": 1203
        },
        {
          "id": 137
        },
        {
          "id": 139
        },
        {
          "id": 13947
        },
        {
          "id": 1523
        },
        {
          "id": 16837
        },
        {
          "id": 1908
        },
        {
          "id": 1909
        },
        {
          "id": 21     (1)
        },
        {
          "id": 225
        },
        {
          "id": 2704
        },
        {
          "id": 2757
        },
        {
          "id": 4299
        },
        {
          "id": 477
        },
        {
          "id": 4965
        },
        {
          "id": 547
        },
        {
          "id": 5479
        },
        {
          "id": 551
        },
        {
          "id": 567    (2)
        },
        {
          "id": 8745
        }
      ]
    1 The 10th document’s id.
    2 The 20th document’s id.

    Next, you specify that you want to delete up to 10 documents, after skipping the first 10.

    DELETE FROM airline 
    WHERE country="France"
    LIMIT 10 OFFSET 10;
    
    SELECT id FROM airline 
    WHERE country="France";

    Now there are 11 documents in this collection with country="France".

    Results
    [
        {
          "id": 1191
        },
        {
          "id": 1203
        },
        {
          "id": 137
        },
        {
          "id": 139
        },
        {
          "id": 13947
        },
        {
          "id": 1523
        },
        {
          "id": 16837
        },
        {
          "id": 1908
        },
        {
          "id": 1909
        },
        {
          "id": 21   (1)
        },
        {
          "id": 8745 (2)
        }
      ]
    1 Documents with the first 10 ids—​the offset—​remain in the airline collection.
    2 After deleting 10 documents—​the limit—​1 more document remains in the collection.