Removes data from one or more columns or removes the entire row.
Removes data from one or more selected columns (data is replaced with null) or removes the entire row when no column is specified. Cassandra deletes data in each selected partition atomically and in isolation.
Deleted data is not removed from disk immediately. Cassandra marks the deleted data with a tombstone and then removes it after the grace period.
DELETE [column_name (term)][, ...] FROM [keyspace_name.] table_name [USING TIMESTAMP timestamp_value] WHERE PK_column_conditions [IF EXISTS | IF static_column_conditions]
||Variable value. Replace with a user-defined value.|
||Optional. Square brackets (
||Group. Parentheses (
||Or. A vertical bar (
||Repeatable. An ellipsis (
||Single quotation (
||Map collection. Braces (
||Set, list, map, or tuple. Angle brackets (
||End CQL statement. A semicolon (
||Separate the command line options from the command arguments with two hyphens (
||Search CQL only: Single quotation marks (
||Search CQL only: Identify the entity and literal value to overwrite the XML element in the schema and solrConfig files.|
- Set column to delete or use a comma separated list of columns. When no column is specified the entire row is deleted.
- Element identifier for collection types, where:
list Index number of item, where 0 is the first. map Element key of item.
- Deletes values older than the timestamp_value.
- Syntax to match PRIMARY KEY values. Separate multiple conditions with AND.Restriction:
- Only equals (=) or IN are supported.
- Ranges (IN) are not supported when specifying a static column condition, see IF condition.
- When removing data from columns in matching rows, you must specify a condition for all primary keys.
- IF EXISTS
- Error when the statement results in no operation.
- IF condition
- Specify conditions for static fields to match. Separate multiple conditions with
AND.Restriction: Modifies the primary key statement, all primary keys required.
Delete data from row
Delete the data in specific columns by listing them after the DELETE command, separated by commas. Change the data in first and last name colums to null.
DELETE firstname, lastname FROM cycling.cyclist_name WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0;
Delete an entire row
Entering no column names after DELETE, removes the entire matching row. Remove a cyclist entry from the cyclist_name table and return an error if no rows match.
DELETE FROM cycling.cyclist_name WHERE id=e7ae5cf3-d358-4d99-b900-85902fda9bb0 IF EXISTS;
IF limits the where
clause, allowing selection based on values in non-PRIMARY KEY columns, such as first and
last name; remove the cyclist record if the first and last name do not
DELETE FROM cycling.cyclist_name WHERE id =e7ae5cf3-d358-4d99-b900-85902fda9bb0 if firstname='Alex' and lastname='Smith';
The results show all the data
[applied] | firstname | lastname -----------+-----------+---------- False | Alex | FRAME
Conditionally deleting columns
In Cassandra 2.0.7 and later, you can conditionally delete columns using IF or IF EXISTS. Deleting a column is similar to making an insert or update conditionally.
Add IF EXISTS to the command to ensure that the operation is not performed if the specified row does not exist:
DELETE id FROM cyclist_id WHERE lastname = 'WELTEN' and firstname = 'Bram' IF EXISTS;
Without IF EXISTS, the command proceeds with no standard output. If IF EXISTS returns true (if a row with this primary key does exist), standard output displays a table like the following:
If no such row exists, however, the conditions returns FALSE and the command fails. In this case, standard output looks like:
If all the conditions return TRUE, standard output is the same as if IF EXISTS returned true (see above). If any of the conditions fails, standard output displays
DELETE id FROM cyclist_id WHERE lastname = 'WELTEN' AND firstname = 'Bram' IF age = 2000;
[applied]column and also displays information about the condition that failed:
Conditional deletions incur a non-negligible performance cost and should be used sparingly.
Deleting old data using TIMESTAMP
The TIMESTAMP is an integer representing microseconds. You can identify the column for deletion using TIMESTAMP.
DELETE firstname, lastname FROM cycling.cyclist_name USING TIMESTAMP 1318452291034 WHERE lastname = 'VOS';
Deleting more than one row
The WHERE clause specifies which row or rows to delete from the table.
DELETE FROM cycling.cyclist_name WHERE id = 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47;
To delete more than one row, use the keyword IN and supply a list of values in parentheses, separated by commas:
DELETE FROM cycling.cyclist_name WHERE firstname IN ('Alex', 'Marianne');
In Cassandra 2.0 and later, CQL supports an empty list of values in the IN clause, useful in Java Driver applications.
Deleting from a collection set, list or map
To delete an element from a map that is stored as one column in a row, specify the column_name followed by the key of the element in square brackets:
DELETE sponsorship ['sponsor_name'] FROM cycling.races WHERE race_name = 'Criterium du Dauphine';
To delete an element from a list, specify the column_name followed by the list index position in square brackets:
DELETE categories FROM cycling.cyclist_history WHERE lastname = 'TIRALONGO';
To delete all elements from a set, specify the column_name by itself:
DELETE sponsorship FROM cycling.races WHERE race_name = 'Criterium du Dauphine';