DELETE

<< DML statements | FB 2.5 Language Reference | EXECUTE BLOCK >>
<< DML statements | FB 2.1 Language Reference | EXECUTE BLOCK >>

DELETE

COLLATE subclause for
text BLOB columns
ORDER BY
PLAN
Relation alias makes real name unavailable
RETURNING
ROWS

`DELETE`

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:

The TRANSACTION directive is only available in ESQL.
In a pure DSQL session, WHERE CURRENT OF isn't of much use, since there exists no DSQL statement to create a cursor.
The PLAN, ORDER BY and ROWS clauses are not available in ESQL.
The RETURNING clause is not available in ESQL.
The INTO <variables> subclause is only available in PSQL.
When returning values into the context variable NEW, this name must not be preceded by a colon (":").

`COLLATE` subclause for text BLOB columns

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'

`ORDER BY`

Available in: DSQL, ESQL

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.

`PLAN`

Available in: DSQL, PSQL

Added in: 2.0

Description

DELETE now allows a PLAN clause, so users can optimize the operation manually.

Relation alias makes real name unavailable

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'

`RETURNING`

Available in: DSQL, PSQL

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:

In DSQL, a statement with a RETURNING clause always returns exactly one row. If no record was actually deleted, the fields in this row are all NULL. This behaviour may change in a later version of Firebird. In PSQL, if no row was deleted, nothing is returned, and the receiving variables keep their existing values.

`ROWS`

Available in: DSQL, PSQL

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:

If m > the total number of rows in the dataset, the entire set is deleted.
If m = 0, no rows are deleted.
If m < 0, an error is raised.

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:

If m > the total number of rows in the dataset, no rows are deleted.
If m lies within the set but n doesn't, the rows from m to the end of the set are deleted.
If m < 1 or n < 1, an error is raised.
If n = m-1, no rows are deleted.
If n < m-1, an error is raised.

ROWS can also be used with the SELECT and UPDATE statements.