DELETE

Removes data by replacing values in specified columns with null, or by dropping an entire row if no column is specified.

If multiple partitions are involved, data in each selected partition is deleted atomically and in isolation.

Data isn’t removed from disk immediately; instead, it is marked with a tombstone. Tombstoned data remains in the database for the duration of gc_grace_seconds. Tombstoned data is scanned by database read operations, but it isn’t included in the returned data. Compaction and repair processes automatically clean up tombstoned data that has exceeded gc_grace_seconds; this is the point at which the data is removed from disk.

DELETE can impact performance, especially when used frequently. All DELETE statements create tombstones that are scanned by read operations until being cleaned up by compaction. Excessive tombstones can degrade read performance and increase the load on compaction processes.

INSERT and UPDATE statements that are upserts (overwrite if exists) are technically deletions because the previously stored data is deleted (marked with tombstones) and replaced in full with the updated data.

Syntax

DELETE [ <column_name> [ <term> ] [ , ... ] ]
  FROM [<keyspace_name>.]<table_name>
  [ USING TIMESTAMP <timestamp_value> ]
  WHERE <PK_column_conditions>
  [ ( IF EXISTS | IF <static_column_conditions> ) ] ;
Legend
Syntax conventions Description

UPPERCASE

Literal keyword.

Lowercase

Not literal.

< >

Variable value. Replace with a user-defined value.

[]

Optional. Square brackets ([]) surround optional command arguments. Do not type the square brackets.

( )

Group. Parentheses ( ( ) ) identify a group to choose from. Do not type the parentheses.

|

Or. A vertical bar (|) separates alternative elements. Type any one of the elements. Do not type the vertical bar.

...

Repeatable. An ellipsis ( ... ) indicates that you can repeat the syntax element as often as required.

'<Literal string>'

Single quotation (') marks must surround literal strings in CQL statements. Use single quotation marks to preserve upper case.

{ <key> : <value> }

Map collection. Braces ({ }) enclose map collections or key value pairs. A colon separates the key and the value.

<<datatype1>,<datatype2>>

Set, list, map, or tuple. Angle brackets ( < > ) enclose data types in a set, list, map, or tuple. Separate the data types with a comma.

<cql_statement>;

End CQL statement. A semicolon (;) terminates all CQL statements.

[--]

Separate the command line options from the command arguments with two hyphens ( -- ). This syntax is useful when arguments might be mistaken for command line options.

' <<schema\> ... </schema\>> '

Search CQL only: Single quotation marks (') surround an entire XML schema declaration.

@<xml_entity>='<xml_entity_type>'

Search CQL only: Identify the entity and literal value to overwrite the XML element in the schema and solrConfig files.

Options

column_name

Set column to delete or use a comma-separated list of columns. When no column is specified, the entire row is deleted.

term

Element identifier for collection types:

  • Lists specify the index number of the item, where 0 is the first.

  • Maps specify the element key of the item.

timestamp_value

Deletes values older than the timestamp_value.

PK_column_conditions

Syntax to match PRIMARY KEY values. Separate multiple conditions with AND.

  • 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.

If this modifies the primary key statement, and all primary keys required.

Examples

The examples in this section use this table:

CREATE TABLE cycling.cyclist_name (
  id UUID PRIMARY KEY,
  lastname text,
  firstname text
);

Delete data in specific columns and rows

To delete data in specific columns, use the DELETE …​ FROM …​ syntax. To specify multiple columns, use commas to separate the column names.

This operation deletes values at the cell level. Because CQL tables have strict schemas that require a value in every cell, the data is replaced with null rather than being truly deleted (nonexistent).

  • Delete (set to null) the value in the lastname column in the cyclist_name table for the row with the specified id:

    DELETE lastname FROM cycling.cyclist_name
  WHERE id = c7fceba0-c141-4207-9494-a29f9809de6f;

  • Delete (set to null) the values in the lastname and firstname columns in the cyclist_name table for the row with the specified id:

    DELETE lastname, firstname FROM cycling.cyclist_name
  WHERE id = c7fceba0-c141-4207-9494-a29f9809de6f;

Delete an entire row

If DELETE doesn’t specify a column, the entire row identified by the WHERE clause is deleted.

The following example removes the entire row for the specified id from the cyclist_name table:

DELETE FROM cycling.cyclist_name
  WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0;

Delete multiple specific rows

To delete multiple rows, use the IN keyword on the WHERE clause. IN accepts a comma-separated list of values enclosed in parentheses. Each value uniquely identifies a row to delete.

IN cannot be used on non-primary key columns.

The following example removes two rows from the cyclist_name table, specified by the given id values:

DELETE FROM cycling.cyclist_name
  WHERE id
  IN ( 5b6962dd-3f90-4c93-8f61-eabfa4a803e2, 220844bf-4860-49d6-9a4b-6b5d3a79cbfb );

IN can be combined with other DELETE options, such as selecting multiple columns to delete from multiple rows.

The IN clause can accept an empty list of values. This can be useful in Apache Cassandra Java driver applications when dynamically constructing IN clauses with potentially empty lists.

Conditionally delete data from a column

Conditional deletions incur a non-negligible performance cost and should be used sparingly.

Use the IF or IF EXISTS clauses to conditionally delete data from columns. This is similar to a conditional UPDATE operation.

IF EXISTS

The IF EXISTS option ensures that the deletion occurs only if the specified row exists:

DELETE id FROM cycling.cyclist_id
  WHERE lastname = 'JONES'
    AND firstname = 'Bram'
  IF EXISTS;

The command returns True if a matching row was found and deleted:

 [applied]
-----------
      True

If no matching row is found, the command does not perform any deletion, and it returns False.

 [applied]
-----------
     False

Without IF EXISTS, the command proceeds silently with no standard output regardless of the outcome.

IF

Use the IF condition to check specific values in the selected row, only performing the deletion if all conditions are met. For example:

  • Delete the id column for Bram WELTEN only if the age column has the value 20:

    DELETE id FROM cycling.cyclist_id
  WHERE lastname = 'WELTEN'
    AND firstname = 'Bram'
  IF age = 20;

  • Delete the entire row for the given id only if the firstname and lastname columns match the specified values:

    DELETE FROM cycling.cyclist_name
  WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa
  IF firstname = 'Michael' AND lastname = 'Smith';

If all conditions return true, then the command is applied, and the following is output:

 [applied]
-----------
      True

If any conditions fail, the command fails, and the output includes information about the conditions that failed:

For example, when IF is used on an invalid column:

InvalidRequest: Error from server: code=2200 [Invalid query]
message="PRIMARY KEY column 'age' cannot have IF conditions"

Or if the column is valid but the condition wasn’t met:

 [applied] | firstname | lastname
-----------+-----------+----------
     False |   Michael | MATTHEWS

Delete data by timestamp

The TIMESTAMP is an integer representing microseconds. Use TIMESTAMP to identify data to delete. The query deletes any rows from a partition older than the timestamp.

DELETE firstname, lastname
  FROM cycling.cyclist_name
  USING TIMESTAMP 1318452291034
  WHERE lastname = 'VOS';

Delete elements from a collection (set, list, or map)

It is possible to delete one or more elements from collection columns. However, these operations can be restricted by conditions like FROZEN or the collection type itself.

Delete all elements from a collection column

To delete all elements from a collection column, specify the column name in the DELETE statement. This operation is the same as a typical DELETE statement targeting a specific non-collection column. The value is replaced with null.

DELETE sponsorship FROM cycling.race_sponsors
  WHERE race_name = 'Giro d''Italia';
Delete a list element by index position

The method of removing elements using an indexed position from a list requires an internal read. In addition, the client-side application could only discover the indexed position by reading the whole list and finding the values to remove, adding additional latency to the operation. If another thread or client prepends elements to the list before the operation is done, incorrect data will be removed.

To delete one element from a list, specify the column name followed by the list index position in square brackets:

DELETE sponsorship[2] FROM cycling.race_sponsors
  WHERE race_year = 2018 AND race_name = 'Tour de France';
Delete a list element with the subtraction operator

Use an UPDATE command with the subtraction operator (-) to remove a list element. This is safer and faster than deleting list elements by index position. For more information, see Insert or update a list.

Delete a map element by key

To delete an element from a map, specify the column name followed by the element key in square brackets:

DELETE teams[2014] FROM cycling.cyclist_teams
  WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;

Other ways to delete data

The DELETE command is used to remove data from a table explicitly. Other ways to delete data include automatic expiration and broad DROP and TRUNCATE commands:

  • USING TTL: Automatically expire data with time-to-live (TTL).

  • ALTER TABLE: Drop one or more columns from a table.

  • DROP TABLE: Delete an entire table.

  • DROP KEYSPACE: Delete an entire keyspace, including all tables and their data.

  • TRUNCATE: Delete all rows in a table, leaving an empty table. By extension, this also drops the data from materialized views based on the table.

Was this helpful?

Give Feedback

How can we improve the documentation?

© Copyright IBM Corporation 2026 | Privacy policy | Terms of use |  Manage Privacy Choices

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: Contact IBM