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

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 delete.

To be able to use the WHERE CURRENT OF clause in DSQL, the cursor name needs to be set on the statement handle before executing the statement.

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

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);

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.

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.

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;

When the SKIP LOCKED clause is specified, records locked by a different transaction are skipped by the statement and are not deleted.

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 delete.

A DELETE statement may optionally include a RETURNING clause to return values from the deleted rows. The clause, if present, need not contain all the relation’s columns and may also contain other columns or expressions.

The user executing the statement needs to have SELECT privileges on the columns specified in the RETURNING clause.

The syntax of the returning_list is similar to the column list of a SELECT clause. It is possible to reference all columns using *, or table_name.*.

• In DSQL, a positioned delete statement (WHERE CURRENT OF …​) with RETURNING always returns a singleton, never a multi-row set. If no records is deleted, the returned columns contain NULL.

• A normal DELETE statement can return zero or more rows; the deletion is executed to completion before rows are returned.

• In PSQL, if a RETURNING clause is present and more than one matching record is found, an error multiple rows in singleton select is raised. This behaviour may change in a future Firebird version.

• The INTO clause is available only in PSQL

  • If no row is 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 Scholars
   |  WHERE firstname = 'Henry' and lastname = 'Higgins'
   |  RETURNING *;
   | 
   |DELETE FROM Dumbbells
   |  ORDER BY iq DESC
   |  ROWS 1
   |  RETURNING lastname, iq into :lname, :iq;