DELETE

Table of Contents

Aliases
WHERE
PLAN
ORDER BY and ROWS
RETURNING

Used for: Deleting rows from a table or view

Available in: DSQL, ESQL, PSQL

Syntax:

DELETE
   FROM {target} [[AS] alias]
   [WHERE {search-conditions | CURRENT OF cursorname}]
   [PLAN plan_items]
   [ORDER BY sort_items]
   [ROWS <m> [TO <n>]]
   [RETURNING <returning_list> [INTO <variables>]]

<m>, <n>     ::=  Any expression evaluating to an integer.
<returning_list>     ::=  ret_value [, ret_value ...]
<variables>  ::=  :varname [, :varname ...]

Table 6.14. Arguments for the DELETE Statement Parameters

Argument	Description
target	The name of the table or view from which the records are to be deleted
alias	Alias for the target table or view
search-conditions	Search condition limiting the set of rows being targeted for deletion
cursorname	The name of the cursor in which current record is positioned for deletion
plan_items	Query plan clause
sort_items	ORDER BY clause
m, n	Integer expressions for limiting the number of rows being deleted
ret_value	An expression to be returned in the RETURNING clause
varname	Name of a PSQL variable

Description: DELETE removes rows from a database table or from one or more of the tables that underlie a view. WHERE and ROWS clauses can limit the number of rows deleted. If neither WHERE nor ROWS is present, DELETE removes all the rows in the relation.

Aliases

If an alias is specified for the target table or view, it must be used to qualify all field name references in the DELETE statement.

Examples:

Supported 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'

Not possible:

delete from Cities C where Cities.name starting 'Alex'

WHERE

The WHERE clause sets the conditions that limit the set of records for a searched delete.

In PSQL, if a named cursor is being used for deleting a set, using the WHERE CURRENT OF clause, the action is limited to the row where the cursor is currently positioned. This is a positioned update.

	Note
	The WHERE CURRENT OF clause is available only in PSQL and ESQL, since there is no statement for creating and manipulating an explicit cursor in DSQL. Searched deletes are also available in PSQL, of course.

Examples:

DELETE FROM People
  WHERE firstname <> 'Boris' AND lastname <> 'Johnson'

DELETE FROM employee e
  WHERE NOT EXISTS(
    SELECT *
    FROM employee_project ep
     WHERE e.emp_no = ep.emp_no);

DELETE FROM Cities
  WHERE CURRENT OF Cur_Cities;  -- ESQL and PSQL only

PLAN

A PLAN clause allows the user to optimize the operation manually.

Example:

DELETE FROM Submissions
  WHERE date_entered < '1-Jan-2002'
  PLAN (Submissions INDEX ix_subm_date);

ORDER BY and ROWS

The ORDER BY clause orders the set before the actual deletion takes place. It only makes sense in combination with ROWS, but is also valid without it.

The ROWS clause limits the number of rows being deleted. Integer literals or any integer expressions can be used for the arguments m and n.

If ROWS has one argument, m, the rows to be deleted 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 deleted
If m = 0, no rows are deleted
If m < 0, an error occurs and the deletion fails

If two arguments are used, m and n, ROWS limits the rows being deleted 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 deleted
If m > 0 and <= the number of rows in the set and n is outside these values, rows from m to the end of the set are deleted
If m < 1 or n < 1, an error occurs and the deletion fails
If n = m - 1, no rows are deleted
If n < m -1, an error occurs and the deletion fails

Examples:

Deleting the oldest purchase:
DELETE FROM Purchases
  ORDER BY date ROWS 1
            
Deleting the highest custno(s):
DELETE FROM Sales
  ORDER BY custno DESC ROWS 1 to 10
            
Deleting all sales, ORDER BY clause pointless:
DELETE FROM Sales
  ORDER BY custno DESC
            
Deleting one record starting from the end, i.e. from Z…:
DELETE FROM popgroups
  ORDER BY name DESC ROWS 1
            
Deleting the five oldest groups:
DELETE FROM popgroups
  ORDER BY formed ROWS 5
            
No sorting (ORDER BY) is specified so 8 found records, starting from the fifth one, will be deleted:
DELETE FROM popgroups
  ROWS 5 TO 12
            

RETURNING

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 the relation's columns and may also contain other columns or expressions.

	Notes
	In DSQL, a statement with RETURNING always returns a singleton, never a mult-row set. If no records are deleted, the returned columns contain `NULL`. This behaviour may change in future Firebird versions The INTO clause is available only in PSQL If the row is not deleted, nothing is returned and the target variables keep their values

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;

	The “Unstable Cursor” Problem
	Because of the way the execution of data-changing DML is implemented in Firebird, up to and including this version, the sets targeted for deletion sometimes produce unexpected results. For more information, refer to The “Unstable Cursor” Problem in the UPDATE section.