FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

MERGE

Merges data from a source set into a target table or updatable view

Syntax
MERGE INTO target [[AS] target_alias]
  USING <table-reference>
  ON <join_condition>
  <merge_when> [<merge_when> ...]
  [PLAN <plan-expr>]
  [ORDER BY <ordering-list>]
  [RETURNING <returning_list> [INTO <variables>]]

<merge_when> ::=
    <merge_when_matched>
  | <merge_when_not_matched_target>
  | <merge_when_not_matched_source>

<merge_when_matched> ::=
  WHEN MATCHED [AND <condition>] THEN
  { UPDATE SET <assignment-list>
  | DELETE }

<merge_when_not_matched_target> ::=
  WHEN NOT MATCHED [BY TARGET] [AND <condition>] THEN
  INSERT [( <column_list> )] [<override_opt>]
  VALUES ( <value_list> )

<merge_when_not_matched_source> ::=
  WHEN NOT MATCHED BY SOURCE [ AND <condition> ] THEN
  { UPDATE SET <assignment-list>
  | DELETE }

<table-reference> ::= <table-primary> | <joined-table>

<table-primary> ::=
    <table-or-query-name> [[AS] correlation-name]
  | [LATERAL] <derived-table> [<correlation-or-recognition>]
  | <parenthesized-joined-table>

<assignment_list ::=
  col_name = <m_value> [, <col_name> = <m_value> ...]]

<override_opt> ::=
  OVERRIDING {USER | SYSTEM} VALUE

<column_list> ::= colname [, colname ...]

<value_list> ::= <m_value> [, <m_value> ...]

<m_value> ::= <value-expression> | 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 MERGE Statement Parameters
Argument Description

target

Name of target relation (table or updatable view)

table-reference

Data source.It can be a table, a view, a stored procedure, a derived table or a parenthesized joined table

target_alias

Alias for the target relation (table or updatable view)

join_conditions

The (ON) condition(s) for matching the source records with those in the target

condition

Additional test condition in WHEN MATCHED or WHEN NOT MATCHED clause

col_name

Name of a column in the target relation

value-expression

The value assigned to a column in the target table.This expression may be a literal value, a PSQL variable, a column from the source, or a compatible context variable

return_expression

The expression to be returned in the RETURNING clauseCan be a column reference to source or target, or a column reference of the NEW or OLD context of the target, or a value.

ret_alias

Alias for the value expression in the RETURNING clause

varname

Name of a PSQL local variable

The MERGE statement merges records from a source <table-reference> into a target table or updatable view.The source may be a table, view or “anything you can SELECT from” in general.Each source record will be used to update one or more target records, insert a new record in the target table, delete a record from the target table or do nothing.

The action taken depends on the supplied join condition, the WHEN clause(s), and the — optional — condition in the WHEN clause.The join condition and condition in the WHEN will typically contain a comparison of fields in the source and target relations.

Multiple WHEN MATCHED and WHEN NOT MATCHED clauses are allowed.For each row in the source, the WHEN clauses are checked in the order they are specified in the statement.If the condition in the WHEN clause does not evaluate to true, the clause is skipped, and the next clause will be checked.This will be done until the condition for a WHEN clause evaluates to true, or a WHEN clauses without condition matches, or there are no more WHEN clauses.If a matching clause is found, the action associated with the clause is executed.For each row in the source, at most one action is executed.If the WHEN MATCHED clause is present, and several records match a single record in the target table, an error is raised.

Contrary to the other WHEN clauses, the WHEN NOT MATCHED BY SOURCE clauses evaluates records in the target which match no record in source.

Warning

At least one WHEN clause must be present.

WHEN NOT MATCHED is evaluated from the source viewpoint, that is, the table or set specified in USING.It has to work this way because if the source record does not match a target record, INSERT is executed.Of course, if there is a target record which does not match a source record, nothing is done.

Currently, in PSQL, the ROW_COUNT variable returns the value 1, even if more than one record is modified or inserted.For details and progress, refer to firebird#4722.

The ORDER BY Clause

The ORDER BY can be used to influence the order in which rows are evaluated.The primary use case is when combined with RETURNING, to influence the order rows are returned.