FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

RETURNING

A DELETE statement may optionally include a RETURNING clause to return values from the deleted rows.The clause, if present, need not contain all the relation’s columns and may also contain other columns or expressions.

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

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

Note

  • In DSQL, a positioned delete statement (WHERE CURRENT OF …​) with RETURNING always returns a singleton, never a multi-row set.If no records is deleted, the returned columns contain NULL.

  • A normal DELETE statement can return zero or more rows;the deletion is executed to completion before rows are returned.

  • 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 INTO clause is available only in PSQL

    • If no row is deleted, nothing is returned and the target variables keep their values
Examples
DELETE FROM Scholars
  WHERE firstname = 'Henry' and lastname = 'Higgins'
  RETURNING lastname, fullname, id;

DELETE FROM Scholars
  WHERE firstname = 'Henry' and lastname = 'Higgins'
  RETURNING *;

DELETE FROM Dumbbells
  ORDER BY iq DESC
  ROWS 1
  RETURNING lastname, iq into :lname, :iq;

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.