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>]]

<m>, <n> ::= Any expression evaluating to an integer.

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.

back to top of page

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'

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.

back to top of page

Available in: DSQL, ESQL

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'

back to top of page

Available in: DSQL, ESQL

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.