Home > Documentation > DELETE Statement

DELETE Statement

Syntax

DELETE FROM table_identifier USING {stream_identifier | stream_expression}
  WHERE predicate
  [RETURNING target_list]

Substitutable Fields

table_identifier:

The unique identifier (name) of the table from which to delete rows.

stream_identifier:

The unique identifier (name) of the stream whose tuples contain the values that will be used to delete rows from the table.

stream_expression:

A StreamSQL statement that produces a stream whose tuples contain the values that will be used to delete rows from the table. The statement must be enclosed within parentheses.

predicate:

The logic used to select specific table content.

target_list:

One or more entries, separated by commas, of the format target_list_entry.

target_list_entry:

A value, of the format scalar_expression [AS output_field_identifier], to be included in the result set returned by the statement.

scalar_expression:

An expression that generates a value for a tuple field that is returned by the delete operation. Values may be obtained from an input tuple field, from a simple function or from the table. Optionally, the name for a value may be modified through an AS clause.

output_field_identifier:

A unique identifier (name) for the tuple field that contains a value returned by the delete operation.

Discussion

The DELETE statement removes a row, or rows, from an existing table.

With the DELETE statement, the USING clause identifies the stream whose tuples contain the values that will be used to identify the rows that will be deleted from the table. Since multiple rows may be deleted, the optional ORDER BY [DESC] LIMIT number clause provides a way to limit the number of rows affected and to organize the result set generated by the RETURNING clause.

In the RETURNING clause, scalar_expression may specify a value from the stream and/or from the table. For example:

DELETE ...
  RETURNING tuple_field_identifier [AS ...][, ...],
            column_identifier [AS ...][, ...]
  

The result set generated by the RETURNING clause must be captured into a stream. You may use the CREATE STREAM statement to define a stream and the INTO keyword to populate the stream with the content generated by the RETURNING clause. Alternatively, in a single statement you may use the => (arrow) operator with a CREATE STREAM statement, as illustrated below.

CREATE STREAM stream_identifier;
DELETE ... RETURNING ... INTO stream_identifier;

Or:

DELETE ... RETURNING ... => CREATE STREAM stream_identifier