UPDATE OR INSERT
Updates existing rows in a table or updatable view, or — if it does not exist — inserts it
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 ...]
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
|