UPDATE OR INSERT

<< UPDATE | FB 2.5 Language Reference | Transaction control statements >>
<< UPDATE | FB 2.1 Language Reference | Transaction control statements >>

UPDATE OR INSERT

Available in: DSQL, PSQL

Added in: 2.1

Description

UPDATE OR INSERT checks if any existing records already contain the new values supplied for the MATCHING columns. If so, those records are updated. If not, a new record is inserted. In the absence of a MATCHING clause, matching is done against the primary key. If a RETURNING clause is present and more than one matching record is found, an error is raised.

Syntax 

 UPDATE OR INSERT INTO
   {tablename | viewname} [(<columns>)]
   VALUES (<values>)
   [MATCHING (<columns>)]
   [RETURNING <values> [INTO <variables>]]

 <columns>    ::= colname [, colname ...]
 <values>     ::= value [, value ...]
 <variables>  ::= :varname [, :varname ...]

Restrictions:

  • No column may appear more than once in the update/insert column list.
  • If the table has no PK, the MATCHING clause becomes mandatory.
  • The INTO <variables> subclause is only available in PSQL.
  • When returning values into the context variable NEW, this name must not be preceded by a colon (“:”).

Example 

 update or insert into Cows (Name, Number, Location)
   values ('Suzie Moo', 3278823, 'Green Pastures')
   matching (Number)
   returning rec_id into :id;

Notes:

  • Matches are determined with IS NOT DISTINCT, not with the "=" operator. This means that one NULL matches another.
  • The optional RETURNING clause:
    • ...may contain any or all columns of the target table, regardless if they were mentioned earlier in the statement, but also other expressions.
    • ...may contain OLD and NEW qualifiers for field names; by default, the new field value is returned.
    • ...returns field values as they are after the BEFORE triggers have run, but before any AFTER triggers.

See also:
UPDATE
UPDATE DELETE
INSERT
SELECT

back to top of page
<< UPDATE | FB 2.5 Language Reference | Transaction control statements >>
<< UPDATE | FB 2.1 Language Reference | Transaction control statements >>