DELETE

Removes entire rows or one or more columns from one or more rows.

Synopsis

DELETE [ column_name [ , column_name ] [ … ] | column_name [ term ] ]
FROM [ keyspace_name. ] table_name 
[ USING TIMESTAMP timestamp_value ]
WHERE row_specification 
[ IF [ EXISTS | condition [ AND condition ] [ … ] ] ]

Table 1. Legend
Uppercase means literal Lowercase means not literal Italics mean optional The pipe (\|) symbol means OR or AND/OR Ellipsis (...) means repeatable A semicolon that terminates CQL statements is not included in the synopsis.

Description

DELETE removes one or more columns from one or more rows in a table, or removes the entire rows. Apache Cassandra™ applies selections within the same partition key atomically and in isolation.

When a column is deleted, it is not removed from disk immediately. The deleted column is marked with a tombstone and then removed after the configured grace period has expired. The optional timestamp defines the new tombstone record.

Parameters:

column_name

The name of the column to be deleted, or a list of column names

term

If column_name refers to a collection (a list or map), the parameter in parentheses indicates the term in the collection to be deleted. For each collection type, use term to specify:

list	index of the element in the list (the first item in the list has index 0, etc.)
map	the key of the element to be deleted

keyspace_name: The name of the keyspace containing the table from which data will be deleted. Not needed if the keyspace has been set for the session with the USE command.

table_name

The name of the table from which data will be deleted

timestamp_value

If a TIMESTAMP is specified, the command only deletes elements older than the timestamp_value (which match the WHERE and optional IF conditions).

row_specification

The WHERE clause must identify the row or rows to be deleted by primary key:

To specify one row, use primary_key_name = primary_key_value. If the primary key is a combination of elements, follow this with AND primary_key_name = primary_key_value .... The WHERE clause must specify a value for every component of the primary key.
To specify more than one row, use primary_key_name IN ( primary_key_value, primary_key_value … ) As in the previous option, each primary_key_name and primary_key_value must match the composition of the primary key.

IF EXISTS / IF condition

An IF clause can limit the command's action on rows that match the WHERE clause. Two options for IF:

Use IF EXISTS to make the DELETE fail if there are no rows that match the WHERE conditions.
Use IF to specify one or more conditions that must test true for the values in the specified row or rows.

If an IF is used, the command returns a result to standard output. See Conditionally deleting columns for examples.

Deleting columns or a row

Delete specific columns by listing them after the DELETE command, separated by commas.

DELETE firstname, lastname FROM cycling.cyclist_name WHERE firstname = 'Alex';

When no column are listed after DELETE, command deletes the entire row or rows specified in the WHERE clause.

DELETE FROM cycling.cyclist_name WHERE firstname IN ('Alex', 'Marianne');

Specifying the table

The table name follows the list of column names and the keyword FROM, preceded by the keyspace name if necessary.

Conditionally deleting columns

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:

Standard output if DELETE ... IF EXISTS returns TRUE

If no such row exists, however, the conditions returns FALSE and the command fails. In this case, standard output looks like:

Standard output if DELETE ... IS EXISTS returns FALSE

Use IF condition to apply tests to one or more column values in the selected row:

DELETE id FROM cyclist_id WHERE lastname = 'WELTEN' AND firstname = 'Bram' IF age = 2000;

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 False in the [applied] column and also displays information about the condition that failed:

Standard output if DELETE ... IF returns FALSE

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');

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[3] 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';