Home > Documentation > INSERT/UPDATE Statement

INSERT/UPDATE Statement

Syntax

INSERT INTO table_identifier (column_identifier[, ...] ) stream_expression
  ON DUPLICATE KEY UPDATE column_identifier=tuple_scalar_expression[, ...]
  [RETURNING target_list]

Substitutable Fields

table_identifier:

The unique identifier (name) of the table into which to insert the new entries.

column_identifier:

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

stream_expression:

A stream expression, for example a SELECT clause against a stream, which provides the values to be written into the table.

tuple_scalar_expression:

An expression that generates a value that will be written to 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 insert or update 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.

output_field_identifier:

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

Discussion

The INSERT/UPDATE statement will insert a new row into an existing table. If a row with the same primary key already exists, the contents of the existing row will be updated.

With the INSERT/UPDATE statement, the INSERT clause is identical to the insert stream query or insert query statement. The UPDATE clause simply needs to list the table fields to be updated and an expression that yields 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:

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

Or

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