FirebirdSQL logo

The SKIP LOCKED Clause

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

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

The RETURNING Clause

An UPDATE statement may include RETURNING to return some values from the updated rows.RETURNING may include data from any column of the row, not only the columns that are updated by the statement.It can include literals or expressions not associated with columns, if there is a need for that.

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

When the RETURNING set contains data from the current row, the returned values report changes made in the BEFORE UPDATE triggers, but not those made in AFTER UPDATE triggers.

The context variables OLD.fieldname and NEW.fieldname can be used as column names.If OLD. or NEW. is not specified, or if the table name (target) is specified instead, the column values returned are the NEW. ones.

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

In DSQL, a positioned update statement (WHERE CURRENT OF …​) with RETURNING always returns a single row, a normal update statement can return zero or more rows.The update is executed to completion before rows are returned.In PSQL, attempts to execute an UPDATE …​ RETURNING that affects multiple rows will result in the error “multiple rows in singleton select”.This behaviour may change in a future Firebird version.

The INTO Sub-clause

In PSQL, the INTO clause can be used to pass the returning values to local variables.It is not available in DSQL.If no records are updated, nothing is returned and variables specified in RETURNING will keep their previous values.

RETURNING Example (DSQL)

UPDATE Scholars
SET firstname = 'Hugh', lastname = 'Pickering'
WHERE firstname = 'Henry' and lastname = 'Higgins'
RETURNING id, old.lastname, new.lastname;