FirebirdSQL logo

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.

The RETURNING Clause

A MERGE statement can contain a RETURNING clause to return rows added, modified or removed.The merge is executed to completion before rows are returned.The RETURNING clause can contain any columns from the target table (or updatable 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.

In PSQL, If a RETURNING clause is present and more than one matching record is found, an error “multiple rows in singleton select” is raised.This behaviour may change in a future Firebird version.

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.

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

Examples 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

EXECUTE PROCEDURE

Executes a stored procedure

Syntax
EXECUTE PROCEDURE procname
   [{ <inparam-list | ( <inparam-list> ) }]
   [RETURNING_VALUES { <outvar-list> | ( <outvar-list ) }]

<inparam-list> ::=
  <inparam> [, <inparam> ...]

<outvar-list> ::=
  <outvar> [, <outvar> ...]

<outvar> ::= [:]varname
Table 1. Arguments for the EXECUTE PROCEDURE Statement Parameters
Argument Description

procname

Name of the stored procedure

inparam

An expression evaluating to the declared data type of an input parameter

varname

A PSQL variable to receive the return value

Executes an executable stored procedure, taking a list of one or more input parameters, if they are defined for the procedure, and returning a one-row set of output values, if they are defined for the procedure.