6.6MERGE

Used forMerging data from a source set into a target relation

Available inDSQL, PSQL

Syntax

   |MERGE INTO target [[AS] target_alias]
   |  USING <source> [[AS] source_alias]
   |  ON <join_condition>
   |  <merge_when> [<merge_when> ...]
   |  [RETURNING <returning_list> [INTO <variables>]]
   | 
   |<merge_when> ::=
   |    <merge_when_matched>
   |  | <merge_when_not_matched>
   | 
   |<merge_when_matched> ::=
   |  WHEN MATCHED [ AND <condition> ] THEN
   |  { UPDATE SET <assignment-list>
   |  | DELETE }
   | 
   |<merge_when_not_matched> ::=
   |  WHEN NOT MATCHED [ AND <condition> ] THEN
   |  INSERT [( <column_list> )] [<override_opt>]
   |  VALUES ( <value_list> )
   | 
   |<source> ::= tablename | (<select_stmt>)
   | 
   |<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 6.19Arguments for the MERGE Statement Parameters
ArgumentDescription

target

Name of target relation (table or updatable view)

source

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

target_alias

Alias for the target relation (table or updatable view)

source_alias

Alias for the source relation or set

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

tablename

Table or view name

select_stmt

Select statement of the derived table

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 clause Can 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 the source 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.

🛑
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, 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.

6.6.1The RETURNING Clause

A MERGE statement that affects at most one row can contain a RETURNING clause to return values added, modified or removed. If a RETURNING clause is present and more than one matching record is found, an error multiple rows in singleton select is raised. The RETURNING clause can contain any columns from the target table (or updateable view), as well as other columns (eg from the source) and expressions.

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

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

Note

The restriction that RETURNING can only be used with a statement that affects at most one row might be removed in a future version.

Column names can be qualified by the OLD or NEW prefix to define exactly what value to return: before or after modification. The returned values include the changes made by BEFORE triggers.

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

For the UPDATE or INSERT action, unqualified column names, or those qualified by the target table name or alias will behave as if qualified by NEW, while for the DELETE action as if qualified by OLD.

The following example modifies the previous example to affect one line, and adds a RETURNING clause to return the old and new quantity of goods, and the difference between those values.

Using MERGE with a RETURNING clause

   |MERGE INTO PRODUCT_INVENTORY AS TARGET
   |USING (
   |  SELECT
   |    SL.ID_PRODUCT,
   |    SUM(SL.QUANTITY)
   |  FROM SALES_ORDER_LINE SL
   |  JOIN SALES_ORDER S ON S.ID = SL.ID_SALES_ORDER
   |  WHERE S.BYDATE = CURRENT_DATE
   |  AND SL.ID_PRODUCT =: ID_PRODUCT
   |  GROUP BY 1
   |) AS SRC (ID_PRODUCT, QUANTITY)
   |ON TARGET.ID_PRODUCT = SRC.ID_PRODUCT
   |WHEN MATCHED AND TARGET.QUANTITY - SRC.QUANTITY <= 0 THEN
   |  DELETE
   |WHEN MATCHED THEN
   |  UPDATE SET
   |    TARGET.QUANTITY = TARGET.QUANTITY - SRC.QUANTITY,
   |    TARGET.BYDATE = CURRENT_DATE
   |RETURNING OLD.QUANTITY, NEW.QUANTITY, SRC.QUANTITY
   |INTO : OLD_QUANTITY, :NEW_QUANTITY, :DIFF_QUANTITY

6.6.2Examples of MERGE

  1. Update books when present, or add new record if absent

      |MERGE INTO books b
      |  USING purchases p
      |  ON p.title = b.title and p.type = 'bk'
      |  WHEN MATCHED THEN
      |    UPDATE SET b.desc = b.desc || '; ' || p.desc
      |  WHEN NOT MATCHED THEN
      |    INSERT (title, desc, bought) values (p.title, p.desc, p.bought);
    
  2. Using a derived table

      |MERGE INTO customers c
      |  USING (SELECT * from customers_delta WHERE id > 10) cd
      |  ON (c.id = cd.id)
      |  WHEN MATCHED THEN
      |    UPDATE SET name = cd.name
      |  WHEN NOT MATCHED THEN
      |    INSERT (id, name) values (cd.id, cd.name);
    
  3. Together with a recursive CTE

       |MERGE INTO numbers
       |  USING (
       |    WITH RECURSIVE r(n) AS (
       |      SELECT 1 FROM rdb$database
       |      UNION ALL
       |      SELECT n+1 FROM r WHERE n < 200
       |    )
       |    SELECT n FROM r
       |  ) t
       |  ON numbers.num = t.n
       |  WHEN NOT MATCHED THEN
       |    INSERT(num) VALUES(t.n);
    
  4. Using DELETE clause

      |MERGE INTO SALARY_HISTORY
      |USING (
      |  SELECT EMP_NO
      |  FROM EMPLOYEE
      |  WHERE DEPT_NO = 120) EMP
      |ON SALARY_HISTORY.EMP_NO = EMP.EMP_NO
      |WHEN MATCHED THEN DELETE
    
  5. The following example updates the PRODUCT_INVENTORY table daily based on orders processed in the SALES_ORDER_LINE table. If the stock level of the product would drop to zero or lower, then the row for that product is removed from the PRODUCT_INVENTORY table.

       |MERGE INTO PRODUCT_INVENTORY AS TARGET
       |USING (
       |  SELECT
       |    SL.ID_PRODUCT,
       |    SUM (SL.QUANTITY)
       |  FROM SALES_ORDER_LINE SL
       |  JOIN SALES_ORDER S ON S.ID = SL.ID_SALES_ORDER
       |  WHERE S.BYDATE = CURRENT_DATE
       |  GROUP BY 1
       |) AS SRC (ID_PRODUCT, QUANTITY)
       |ON TARGET.ID_PRODUCT = SRC.ID_PRODUCT
       |WHEN MATCHED AND TARGET.QUANTITY - SRC.QUANTITY <= 0 THEN
       |  DELETE
       |WHEN MATCHED THEN
       |  UPDATE SET
       |    TARGET.QUANTITY = TARGET.QUANTITY - SRC.QUANTITY,
       |    TARGET.BYDATE = CURRENT_DATE
    

See alsoSection 6.1, “SELECT, Section 6.2, “INSERT, Section 6.3, “UPDATE, Section 6.4, “UPDATE OR INSERT, Section 6.5, “DELETE