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 optional INTO sub-clause is only valid in PSQL.

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.

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

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”