DELETE

DELETE — delete rows from a table

Syntax

DELETE FROM [schema].table_name
    [ WHERE condition ]
    [ ORDER BY { order_list [ COLLATE collation_name ] [ ASC | DESC] } [, ...] ]
    [ LIMIT count ]

Syntax using JOIN

DELETE FROM [schema].table_name
        [<join-condition>]
    [ WHERE condition ]
    [ ORDER BY { order_list[ COLLATE collation_name ] [ ASC | DESC] } [, ...] ]
    [ LIMIT count ]

Also see Example 5: Delete a table, using JOIN

Description

DELETE removes rows from the specified table as specified by the WHERE clause. If the WHERE clause is absent, the effect is to delete all rows in the table. The result is a valid but empty table.

The user:

  • must have the DELETE privilege on the table in order to delete from it.

  • must have the SELECT privilege on all tables selected from in WHERE clause if the WHERE clause is a subquery.

  • also needs SELECT privilege on any tables referenced in the join conditions of the FROM clause.

If the ORDER BY clause is specified, the rows are deleted in the specified order. If the ORDER BY is not given, the rows are deleted in whatever order the system finds fastest to achieve. Use the keyword COLLATE to change the character classification behavior of the sort order.
If the LIMIT clause is specified, a subset of rows will be deleted.

Parameters

table_name

Name (optionally schema-qualified) of an existing table or updatable view (see Using Updatable Views).

<join-condition>

The defined table whose records are deleted must always appear in the leftmost side. Only the ON keyword is supported.

WHERE condition

This defines the filter criteria for determining which rows in the table will attempt to be deleted. This condition can be in the form of a value expression that returns a value of type boolean, where a true result is returned for each row selected, or in the form of a valid subquery SELECT. Refer to SELECT for a further description of a valid subquery.

ORDER BY order_list

The optional ORDER BY causes the rows to be deleted in an order according to the specified expressions in the ORDER BY order_list. If two rows are equal according to the left most expression, they are compared according to the next expression and so on. If they are equal according to all specified expressions, they are deleted in an implementation-dependent order.

COLLATE collation_type

A Collation type can be defined to change the character classification behavior of the deletion sort order. Supported collation types include: case_insensitive and "8859-1U". Both collation types are synonyms for each other, so COLLATE case_insensitive will delete in the same order as COLLATE "8859-1U".

ASC | DESC

One may add the keyword ASC (ascending) or DESC (descending) after any item in the order_list in the ORDER BY clause. If not specified, ASC is assumed by default.

LIMIT

count

specifies the maximum number of rows to delete.

When LIMIT is specified, only count rows are deleted.

Examples

Example 1: Delete all rows in a table that meet the value expression criteria.
DELETE FROM teams
     WHERE teamid IN ('COB','BOS','AND');
Example 2: Delete all rows in a table, but only if they exist in another table
DELETE FROM PLAYERS p
     WHERE EXISTS (SELECT 'x' FROM SCORING s
                 WHERE s.playerid = p.playerid
                  AND s.year = p.lastnhl);
Example 3: Delete all rows in a table, but only if they do not exist in another table
DELETE FROM PLAYERS p
     WHERE NOT EXISTS (SELECT 'x' FROM SCORING s
                    WHERE s.playerid = p.playerid
                      AND s.year = p.firstnhl);
Example 4: Delete ten rows in a table, and delete rows in order of their teamid column
DELETE FROM teams ORDER BY teamid LIMIT 10;
Example 5: Delete a table, using JOIN
DELETE FROM PLAYERS p
LEFT JOIN SCORING s
ON (s.playerid = p.playerid)
WHERE s.year = p.lastnhl;
Example 6: Delete from WHERE on partitioned tables
DROP TABLE ent IF EXISTS;

CREATE TABLE ent(pk STRING, partition INT) PARTITION BY RANGE (partition) (
PARTITION p0 VALUES LESS THAN (1) STORE IN cust_sg_000
PARTITION p1 VALUES LESS THAN (2) STORE IN cust_sg_001
PARTITION p2 VALUES LESS THAN (3) STORE IN cust_sg_002
PARTITION p3 VALUES LESS THAN (4) STORE IN cust_sg_003
);

INSERT INTO ent(pk,partition) VALUES ('A', 0);
INSERT INTO ent(pk,partition) VALUES ('C', 1);
INSERT INTO ent(pk,partition) VALUES ('D', 1);
INSERT INTO ent(pk,partition) VALUES ('E', 2);
INSERT INTO ent(pk,partition) VALUES ('F', 2);
INSERT INTO ent(pk,partition) VALUES ('G', 3);
INSERT INTO ent(pk,partition) VALUES ('H', 3);

SELECT pk FROM ent WHERE pk IN ('A','B','C','D','E','F','G','H');
DELETE FROM ent WHERE pk IN ('A','B','C','D','E','F','G','H');SELECT pk FROM ent;

DELETE FROM ent WHERE pk = 'A' OR pk = 'B' OR pk = 'C' OR pk = 'D' OR pk = 'E' OR pk = 'F' OR pk = 'G' OR pk = 'H'

SELECT pk FROM ent;