6.4. UPDATE OR INSERT

Updates existing rows in a table or updatable view, or — if it does not exist — inserts it

Syntax 

   |UPDATE OR INSERT INTO
   |  target [(<column_list>)]
   |  [<override_opt>]
   |  VALUES (<value_list>)
   |  [MATCHING (<column_list>)]
   |  [ORDER BY <ordering-list>]
   |  [ROWS <m> [TO <n>]]
   |  [RETURNING <returning_list> [INTO <variables>]]
   | 
   |<column_list> ::= col_name  [, col_name ...]
   | 
   |<override_opt> ::=
   |  OVERRIDING {USER | SYSTEM} VALUE
   | 
   |<value_list> ::= <ins_value> [, <ins_value> ...]
   | 
   |<ins_value> ::= <value> | DEFAULT
   | 
   |<returning_list> ::= * | <output_column> [, <output_column]
   | 
   |<output_column> ::=
   |    target.* | NEW.* | OLD.*
   |  | <return_expression> [COLLATE collation] [[AS] alias]
   | 
   |<return_expression> ::=
   |    <value-expression>
   |  | [target.]col_name
   |  | NEW.col_name
   |  | OLD.col_name
   | 
   |<value-expression> ::=
   |    <literal>
   |  | <context-variable>
   |  | any other expression returning a single
   |    value of a Firebird data type or NULL
   | 
   |<variables> ::= [:]varname [, [:]varname ...]

Table 6.17. Arguments for the UPDATE OR INSERT Statement Parameters
ArgumentDescription

target

The name of the table or view where the record(s) is to be updated or a new record inserted

col_name

Name of a column in the table or view

value-expression

An expression whose value is to be used for inserting or updating the table, or returning a value

return_expression

An expression returned in the RETURNING clause

varname

Variable name — PSQL only

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 columns in the MATCHING clause or primary key columns are equal. Matching is done with the IS NOT DISTINCT operator, so one NULL matches another.

Restrictions

  • If the table has no primary key, the MATCHING clause is 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 (:).

6.4.1. The ORDER BY and ROWS Clauses

See Section 6.3.4, “The ORDER BY and ROWS Clauses” for UPDATE.

6.4.2. 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.

The user executing the statement needs to have SELECT privileges on the columns specified in the RETURNING clause.

The syntax of the returning_list is similar to the column list of a SELECT clause. It is possible to reference all columns using *, or table_name.*, NEW.* and/or OLD.*.

In DSQL, a statement with a RETURNING clause can return zero or more rows. The update or insert is executed to completion before rows are returned. In PSQL, if a RETURNING clause is present and more than one matching record is found, an error multiple rows in singleton select is raised. This behaviour may change in a future Firebird version.

The optional INTO sub-clause is only valid in PSQL.

6.4.3. Example of UPDATE OR INSERT

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 optional.

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