Firebird 2.5 Language ReferenceFirebird 2.5 Language ReferenceData Manipulation (DML) Statements → UPDATE OR INSERT
Firebird Firebird Prev: UPDATEFirebird 2.5 Language ReferenceUp: Data Manipulation (DML) StatementsNext: DELETE

UPDATE OR INSERT

Table of Contents

The RETURNING clause

Suggested reading: Negative impact of indices to INSERT, UPDATE and DELETE performance in Firebird SQL

Used for: Updating an existing record in a table or, if it does not exist, inserting it

Available in: DSQL, PSQL

Syntax: 

UPDATE OR INSERT INTO
   {target} [(<column_list>)]
   VALUES (<value_list>)
   [MATCHING (<column_list>)]
   [RETURNING <values> [INTO <variables>]]

<column_list>      ::=  colname  [, colname  ...]
<value_list>       ::=  value    [, value    ...]
<ret_values>       ::=  ret_value    [, ret_value    ...]
<variables>    ::=  :varname [, :varname ...]
        

Table 6.13. Arguments for the UPDATE OR INSERT Statement Parameters

Argument Description
target The name of the table or view where the record[s] is to be updated or a new record inserted
colname Name of a column in the table or view
value An expression whose value is to be used for inserting or updating the table
ret_value An expression returned in the RETURNING clause
varname Variable name—PSQL only


Description: UPDATE OR INSERT inserts a new record or updates one or more existing records. The action taken depends on the values provided for the columns in the MATCHING clause (or, if the latter is absent, in the primary key). If there are records found matching those values, they are updated. If not, a new record is inserted.

A match only counts if all the values in the MATCHING or PK columns are equal. Matching is done with the IS NOT DISTINCT operator, so one NULL matches another.

[Note] Restrictions
  • If the table has no PK, the MATCHING clause becomes mandatory.

  • In the MATCHING list as well as in the update/insert column list, each column name may occur only once.

  • The “INTO <variables>” subclause is only available in PSQL.

  • When values are returned into the context variable NEW, this name must not be preceded by a colon (“:”).

The RETURNING clause

The optional RETURNING clause, if present, need not contain all the columns mentioned in the statement and may also contain other columns or expressions. The returned values reflect any changes that may have been made in BEFORE triggers, but not those in AFTER triggers. OLD.fieldname and NEW.fieldname may both be used in the list of columns to return; for field names not preceded by either of these, the new value is returned.

In DSQL, a statement with a RETURNING clause always returns exactly one row. If a RETURNING clause is present and more than one matching record is found, an error is raised. This behaviour may change in a later version of Firebird.

Example: Modifying data in a table, using UPDATE OR INSERT in a PSQL module. The return value is passed to a local variable, whose colon prefix is not optional.

UPDATE OR INSERT INTO Cows (Name, Number, Location)
  VALUES ('Suzy Creamcheese', 3278823, 'Green Pastures')
  MATCHING (Number)
  RETURNING rec_id into :id;

[Important] The “Unstable Cursor” Problem

Because of the way the execution of data-changing DML is implemented in Firebird, up to and including this version, the sets targeted for updating sometimes produce unexpected results. For more information, refer to The “Unstable Cursor” Problem in the UPDATE section.

Prev: UPDATEFirebird 2.5 Language ReferenceUp: Data Manipulation (DML) StatementsNext: DELETE
Firebird 2.5 Language ReferenceFirebird 2.5 Language ReferenceData Manipulation (DML) Statements → UPDATE OR INSERT