FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

The ORDER BY and ROWS Clauses

The ORDER BY and ROWS clauses make sense only when used together.However, they can be used separately.

If ROWS has one argument, m, the rows to be updated will be limited to the first m rows.

Points to note

  • If m > the number of rows being processed, the entire set of rows is updated

  • If m = 0, no rows are updated

  • If m < 0, an error occurs and the update fails

If two arguments are used, m and n, ROWS limits the rows being updated to rows from m to n inclusively.Both arguments are integers and start from 1.

Points to note

  • If m > the number of rows being processed, no rows are updated

  • If n > the number of rows, rows from m to the end of the set are updated

  • If m < 1 or n < 1, an error occurs and the update fails

  • If n = m - 1, no rows are updated

  • If n < m -1, an error occurs and the update fails

ROWS Example
UPDATE employees
SET salary = salary + 50
ORDER BY salary ASC
ROWS 20;

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.