DELETE

Documentation

VoltDB Home » Documentation » Using VoltDB

DELETE

DELETE — Deletes one or more records from the database.

Synopsis

DELETE FROM table-name
[WHERE [NOT] boolean-expression [ {AND | OR} [NOT] boolean-expression]...]
[ORDER BY {column-name [ ASC | DESC ]}[,...] [LIMIT integer] [OFFSET integer]]

Description

The DELETE statement deletes rows from the specified table that meet the constraints of the WHERE clause. The following limitations are important to note when using the DELETE statement in VoltDB:

  • The DELETE statement can operate on only one table at a time (no joins or subqueries).

  • The WHERE expression supports the boolean operators: equals (=), not equals (!= or <>), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), IS NULL, AND, OR, and NOT. Note, however, although OR is supported syntactically, VoltDB does not optimize these operations and use of OR may impact the performance of your queries.

  • The ORDER BY clause lets you order the selection results and then select a subset of the ordered records to delete. For example, you could delete only the five oldest records, chronologically, sorting by timestamp:

    DELETE FROM events ORDER BY event_time ASC LIMIT 5;

    Similarly, you could choose to keep only the five most recent:

    DELETE FROM events ORDER BY event_time DESC OFFSET 5;
  • When using ORDER BY, the resulting sort order must be deterministic. In other words, the ORDER BY must include enough columns to uniquely identify each row. (For example, listing all columns or a primary key.)

  • You cannot use ORDER BY to delete rows from a partitioned table in a multi-partitioned query. In other words, for partitioned tables DELETE... ORDER BY must be executed as part of a single-partitioned stored procedure or as an ad hoc query with a WHERE clause that uniquely identifies the partitioning column value.

Examples

The following example removes rows from the EMPLOYEE table where the EMPLOYEE_ID column is equal to 145303.

DELETE FROM employee WHERE employee_id = 145303;

The following example removes rows from the BID table where the BIDDERID is 12345 and the BIDPRICE is less than 100.00.

DELETE FROM bid WHERE bidderid=12345 AND bidprice<100.0;
>