Alpha

These pages are built from main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

DELETE

Statements

NAME

DELETE: delete rows

SYNOPSIS

<delete statement: searched> ::=
    DELETE [ WITH <XML lexically scoped options> ] FROM <table name>
    [ FOR PORTION OF <period name> FROM <point in time> TO <point in time> ]
    [ WHERE <search condition> ]

DESCRIPTION

DELETE removes the rows of a table, or through an updatable view of the table it is drawn from, for which the search condition is true; every row, without WHERE. The search condition is evaluated for every row before any is deleted. A foreign key that references a row deleted acts as it says: refuses the delete, deletes or changes the rows that refer to it, or sets their columns null or to their defaults. A DELETE that deletes no row completes with no data.

The table's triggers run as CREATE TRIGGER says: its BEFORE triggers once the rows are found, before any is deleted, its AFTER triggers at the end of the statement. A view with an INSTEAD OF trigger of the statement's event runs it in place of the change, and nothing is written through the view: its row count is of the rows the trigger was given. An updatable view over such a view runs its trigger the same way, and so does the positioned form through a cursor over either. The rows a referential action deletes or changes run their own table's triggers too.

FOR PORTION OF a period of the table (see CREATE TABLE) deletes only the time from its first point to its second. It deletes the rows the WHERE condition is true of whose period overlaps that time, and keeps each one's parts before and after it as rows of their own, whose period ends where the portion begins or begins where it ends. A part kept takes its identity column's next value and its generated columns again, and is inserted as an INSERT inserts a row, its INSERT triggers running; the constraints judge it with the rest as the statement ends. Its row count is of the rows deleted.

WITH's XML options, XMLNAMESPACES and XMLBINARY, are in scope of the whole statement, its search condition and subqueries too: see XML Values.

The positioned form, WHERE CURRENT OF a cursor, runs only in a program that embeds turborel; see DECLARE CURSOR, and, of a cursor named by an extended name, ALLOCATE CURSOR.

RULES

  1. The table exists and is not read-only: a base table, or a view that can be updated.
  2. The search condition contains no set function outside a subquery.
  3. The rows left, and those referential actions change, satisfy the constraints.
  4. The transaction is not READ ONLY, but for a temporary table.
  5. FOR PORTION OF names an application-time period of the table, a base table, not SYSTEM_TIME, a reserved word. Its points in time are datetimes comparable with the period's columns, reference no column of the table, and invoke no function that is not deterministic or modifies SQL-data.
  6. A row deleted from a system-versioned table is kept as a historical row, ending at the transaction timestamp.

PRIVILEGES

DELETE on the table. SELECT on each other table a subquery reads.

ERRORS

SQLSTATE SQLCODE When
2A000 -103 The table does not exist or is read-only; the search condition has a set function outside a subquery; a rule of FOR PORTION OF is broken (rule 5); WITH's XML options break a rule of XML Values
2A000 -104 Its user lacks DELETE on the table, or SELECT on a table read
23000 -201 A row deleted is referenced by a foreign key whose action refuses it, or a referential action breaks a constraint
2201H -301 A row of a system-time period begins after the transaction timestamp: another transaction changed it later
TB911 -911 A row was written by another transaction, not committed under LOCKS.WAIT OFF, or committed after this one's snapshot at REPEATABLE READ or SERIALIZABLE; or a wait for it would deadlock or passed LOCKS.TIMEOUT_MS: the statement is undone, and may be run again
25000 -917 The transaction is READ ONLY
02000 100 Not an error: no row was deleted

A statement that fails deletes no row.

EXAMPLES

Of the table PARTS of UPDATE, as it was before those updates:

DELETE FROM PARTS WHERE QTY = 0;
SELECT ID FROM PARTS ORDER BY ID;
DELETE FROM PARTS WHERE ID = 99;
OK
           ID
--------------
            1
(1 row)
no data
DELETE FROM PARTS WHERE SUM(QTY) > 0;
turborel: SQLCODE -103 (SQLSTATE 2A000): a set function needs an argument source (a <table expression>), which this <search condition> does not have [X3.135-1986 §5.8 SR1] (at character 24)

Of a table of prices through time, a week of January deleted:

CREATE TABLE OFFERS (ITEM INTEGER, PRICE INTEGER, S DATE NOT NULL, E DATE NOT NULL, PERIOD FOR VALID (S, E));
INSERT INTO OFFERS VALUES (1, 10, DATE '2026-01-01', DATE '2026-02-01');
DELETE FROM OFFERS FOR PORTION OF VALID FROM DATE '2026-01-10' TO DATE '2026-01-17';
SELECT * FROM OFFERS ORDER BY S;
OK
OK
OK
         ITEM |        PRICE | S          | E
--------------+--------------+------------+------------
            1 |           10 | 2026-01-01 | 2026-01-10
            1 |           10 | 2026-01-17 | 2026-02-01
(2 rows)

STANDARD

ISO/IEC 9075:1992 §13.7, the <delete statement: searched>, and X3.135-1986 §8.5. FOR PORTION OF is ISO/IEC 9075-2:2023 §14.9 SR7a and §15.8 GR9, Feature T181. WITH's XML options are ISO/IEC 9075-14:2006 draft §14.3, Features X082 and X132. Referential actions are SQL-92's [§11.8]. TB911 is this implementation's own.

SEE ALSO

INSERT, UPDATE, DROP TABLE, CREATE TRIGGER, Constraints