Home > Documentation > REPLACE Statement

REPLACE Statement

Syntax

REPLACE INTO table_identifier (column_identifier[, ...] ) stream_expression
  [RETURNING target_list]

Substitutable Fields

table_identifier:

The unique identifier (name) of the table in which to replace values.

column_identifier:

The unique identifier (name) of a column into which a value will be replaced.

stream_expression:

A stream expression, for example a SELECT clause against a stream, which provides the values used to modify the table.

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 the tuple that is returned by the replace operation. Values may be obtained from a tuple field or from a simple function. Optionally, the name for a value may be modified through an AS clause.

Discussion

The REPLACE statement deletes and replaces an existing row in a table.

With the REPLACE statement, the INTO clause lists the table fields that will be modified. The stream_expression is a SELECT clause against a stream whose tuples contain the data that will be entered into the table. Its target list identifies the tuple fields that will provide the content for each table field.

If all of the tuple fields will be entered into the table, and the fields are in an order that corresponds to the order of table fields, you may use * as the target list. Otherwise the target list must explicitly extract each tuple field, as illustrated below.

SELECT * FROM stream_identifier
SELECT tuple_field_identifier[, ...] FROM stream_identifier

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

REPLACE ...
  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;
REPLACE ... RETURNING ... INTO stream_identifier;

Or

REPLACE ... RETURNING ... => CREATE STREAM stream_identifier