FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

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

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.

Note
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 (“:”).

The ORDER BY and ROWS Clauses

See [fblangref50-dml-update-orderbyclause] for UPDATE.