Home > Documentation > UPDATE Statement

UPDATE Statement

Syntax

UPDATE table_identifier USING {stream_identifier | stream_expression}
  SET column_identifier=tuple_scalar_expression[, ...]
  WHERE predicate
  [RETURNING target_list]

Substitutable Fields

table_identifier:

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

stream_identifier:

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

stream_expression:

A StreamSQL statement that produces a stream. The statement must be enclosed within parentheses.

column_identifier:

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

tuple_scalar_expression:

An expression that generates a value that will be used to update the table.

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

Discussion

The UPDATE statement changes the values stored in a row of an existing table.

With the UPDATE statement, the USING clause identifies the stream whose tuples contain the values that will be used to update the table. The SET clause lists the table fields that will be modified and, for each, an expression that is the source of the new value. This expression may simply be the name of a tuple field or a value derived by applying expression operators and/or simple functions to the values currently in the table or in the tuple.

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

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

Or

UPDATE ... RETURNING ... => CREATE STREAM stream_identifier