FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

SKIP LOCKED

When the SKIP LOCKED clause is specified, records locked by a different transaction are skipped by the statement and are not deleted.

When a ROWS clause is specified, the “skip locked” check is performed after skipping the requested number of rows specified, and before counting the number of rows to delete.

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;