CQL for Cassandra 2.x

DELETE

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

Synopsis

DELETE column_name, ... | ( column_name term )  
FROM keyspace_name.table_name
USING TIMESTAMP integer
WHERE row_specification
( IF ( EXISTS | ( condition(  AND condition ) . . .  ) ) )

term is:

[ list_position ] | key_value

row_specification is one of:

primary_key_name = key_value
primary_key_name IN ( key_value, key_value, ...)(
condition is:
column_name = key_value
| column_name [list_position] = key_value

Synopsis Legend

  • Uppercase means literal
  • Lowercase means not literal
  • Italics mean optional
  • The pipe (|) symbol means OR or AND/OR
  • Ellipsis (...) means repeatable
  • Orange ( and ) means not literal, indicates scope

A semicolon that terminates CQL statements is not included in the synopsis.

Description

A DELETE statement removes one or more columns from one or more rows in a table, or it removes the entire row if no columns are specified. Cassandra applies selections within the same partition key atomically and in isolation.

Deleting columns or a row

After the DELETE keyword, optionally list column names, separated by commas.

DELETE col1, col2, col3 FROM Planeteers WHERE userID = 'Captain';

When no column names are specified, the entire row(s) specified in the WHERE clause are deleted.

DELETE FROM MastersOfTheUniverse WHERE mastersID IN ('Man-At-Arms', 'Teela');

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.

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. Conditional deletions incur a non-negligible performance cost and should be used sparingly.

Specifying the table

The table name follows the list of column names and the keyword FROM.

Deleting old data

You can identify the column for deletion using a timestamp.

DELETE email, phone
  FROM users
  USING TIMESTAMP 1318452291034
  WHERE user_name = 'jsmith';

The TIMESTAMP input is an integer representing microseconds. The WHERE clause specifies which row or rows to delete from the table.

DELETE col1 FROM SomeTable WHERE userID = 'some_key_value';

This form provides a list of key names using the IN notation and a parenthetical list of comma-delimited key names.

DELETE col1 FROM SomeTable WHERE userID IN (key1, key2);
      DELETE phone FROM users WHERE user_name IN ('jdoe', 'jsmith');

In Cassandra 2.0 and later, CQL supports an empty list of values in the IN clause, useful in Java Driver applications when passing empty arrays as arguments for the IN clause.

Using a collection set, list or map

To delete an element from the map, use the DELETE command and enclose the timestamp of the element in square brackets:

DELETE todo ['2012-9-24'] FROM users WHERE user_id = 'frodo';

To remove an element from a list, use the DELETE command and the list index position in square brackets:

DELETE top_places[3] FROM users WHERE user_id = 'frodo';

To remove all elements from a set, you can use the DELETE statement:

DELETE emails FROM users WHERE user_id = 'frodo';
Show/hide