Available in: DSQL, ESQL, PSQL
Description
Deletes rows from a database table (or from one or more tables underlying a view), depending on the WHERE and ROWS clauses.
Syntax
DELETE [TRANSACTION name] FROM {tablename | viewname} [[AS] alias] [WHERE {search-conditions | CURRENT OF cursorname}] [PLAN plan_items] [ORDER BY sort_items] [ROWS <m> [TO <n>]] [RETURNING values [INTO <variables>]] <m>, <n> ::= Any expression evaluating to an integer. <variables> ::= :varname [, :varname ...]
Restrictions:
Added in: 2.0
Description
COLLATE subclauses are now also supported for text BLOBs.
Example
delete from MyTable where NameBlob collate pt_br = 'João'
Added in: 2.0
Description
DELETE now allows an ORDER BY clause. This only makes sense in combination with ROWS, but is also valid without it.
Added in: 2.0
Description
DELETE now allows a PLAN clause, so users can optimize the operation manually.
Changed in: 2.0
Description
If you give a table or view an alias in a Firebird 2.0 or above statement, you must use the alias, not the table name, if you want to qualify fields from that relation.
Examples
Correct usage:
delete from Cities where name starting 'Alex' delete from Cities where Cities.name starting 'Alex' delete from Cities C where name starting 'Alex' delete from Cities C where C.name starting 'Alex'
No longer possible:
delete from Cities C where Cities.name starting 'Alex'
Added in: 2.1
Description
A DELETE statement removing at most one row may optionally include a RETURNING clause in order to return values from the deleted row. The clause, if present, need not contain all of the relation's columns and may also contain other columns or expressions.
Examples
delete from Scholars where firstname = 'Henry' and lastname = 'Higgins' returning lastname, fullname, id delete from Dumbbells order by iq desc rows 1 returning lastname, iq into :lname, :iq;
Notes:
Added in: 2.0
Description
Limits the amount of rows deleted to a specified number or range.
Syntax
ROWS <m> [TO <n>] <m>, <n> ::= Any expression evaluating to an integer.
With a single argument m, the deletion is limited to the first m rows of the dataset defined by the table or view and the optional WHERE and ORDER BY clauses.
Points to note:
With two arguments m and n, the deletion is limited to rows m to n inclusively. Row numbers are 1-based.
Points to note when using two arguments:
ROWS can also be used with the SELECT and UPDATE statements.