Firebird Documentation IndexFirebird 2.5 Language Ref. UpdateDML statements → UPDATE OR INSERT
Firebird Home Firebird Home Prev: UPDATEFirebird Documentation IndexUp: DML statementsNext: 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 values are returned into the context variable NEW, this name must not be preceded by a colon (:).

Example: 

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

Notes: 

Prev: UPDATEFirebird Documentation IndexUp: DML statementsNext: Transaction control statements
Firebird Documentation IndexFirebird 2.5 Language Ref. UpdateDML statements → UPDATE OR INSERT