Archive
Archive
DELETE — Deletes one or more records from the database.
DELETE FROM table-name
[WHERE [NOT] boolean-expression [ {AND |
OR} [NOT] boolean-expression]...]
[ORDER BY {column-name [ ASC | DESC ]}[,...] [LIMIT integer] [OFFSET
integer]]
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. It does not support joins. However, it does support subqueries in the WHERE expression.
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.
You can use subqueries in the WHERE clause of the DELETE statement, with the following provisions:
See the description of subqueries in the SELECT statement for general rules concerning the construction of subqueries.
In a multi-partition procedure, subqueries of the DELETE statement can only reference replicated tables.
In single-partitioned procedures, the subquery can reference both partitioned and replicated tables.
For ad hoc DELETE statements, the same rules apply except the SQL statement itself determines whether VoltDB executes it as a single-partitoned or multi-partitioned procedure. Statements that delete rows from a partitioned table based on a specific value of the partitioning column are executed as single-partitioned procedures. All other statements are multi-partitioned.
Subqueries are not allowed in a CREATE TABLE statement that includes DELETE syntax as part of a LIMIT PARTITION ROW clause.
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, event_id ASC LIMIT 5;
Similarly, you could choose to keep only the five most recent:
DELETE FROM events ORDER BY event_time, event_id 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.
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;
V9.x Documentation