Please use the form below to provide your feedback. Because your feedback is valuable to us, the information you submit in this form is recorded in our issue tracking system (JIRA), which is publicly available. You can track the status of your feedback using the ticket number displayed in the dialog once you submit the form.

DELETE Statements

  • Capella Columnar
  • reference
    +
    This topic describes how you use DELETE statements to delete objects from a standalone collection.

    Syntax

    Delete EBNF 
    Delete ::=  "DELETE" "FROM" QualifiedName
            ("AS" OutputAlias)?
            ("WHERE" Expr)?
            ("RETURNING" Expr)?
    Delete Diagram
    "DELETE" "FROM" QualifiedName ("AS" OutputAlias)? ("WHERE" Expr)? ("RETURNING" Expr)?

    The first QualifiedName must resolve to a standalone collection or synonym.

    Examples

    This example deletes all orders placed before 2020-07-01 from a standalone collection named Orders:

      DELETE FROM database_name.scope_name.Orders
  WHERE order_date < "2020-07-01";

    After you use DELETE, you can run ANALYZE COLLECTION on the collection to update the data sample used by cost-based optimization (CBO). See Cost-Based Optimizer for Capella Columnar Services.

    Show additional example

    This example deletes orders made by a customer named T. Cody and returns the values of the orderno field for those deleted orders:

      DELETE FROM database_name.scope_name.Orders AS ord
  WHERE ord.custid = (
     SELECT VALUE cust.custid
     FROM Customers AS cust
     WHERE cust.name = "T. Cody"
  )[0]
  RETURNING VALUE ord.orderno;

    The output of the RETURNING clause is:

    1002
1007
1008
1009

    Arguments

    WHERE

    The optional WHERE clause specifies a condition that the objects in the target collection must satisfy for the statement to delete them. It can include uncomplicated predicates as well as other subqueries referring to other existing collections. The default database for the WHERE expression is the target collection’s database. There’s one variable in scope for the WHERE expression. If specified, OutputAlias defines the variable’s name. Otherwise, the variable’s name is the target collection’s name.

    RETURNING

    Adding an optional RETURNING clause causes the statement to return a result for each object deleted, identified by the OutputAlias or the collection name. The clause can contain subqueries, although they cannot refer to any collections in their FROM clauses, making them object-local in nature.

    Errors encountered during DELETE cancels the action and leaves the target dataset in its pre-DELETE state.

    See Also