DELETE

Removes data from one or more selected columns (data is replaced with null) or removes the entire row when no column is specified. Deletes data in each selected partition atomically and in isolation. Data is not removed from disk immediately; it is marked with a tombstone and then removed after the grace period.

Using DELETE can impact performance. Tombstones are created, causing stale data to be read until removed by a compaction.

Syntax

DELETE [ <column_name> [ <term> ] [ , ... ] ]
  FROM [<keyspace_name>.]<table_name>
  [ USING TIMESTAMP <timestamp_value> ]
  WHERE <PK_column_conditions>
  [ ( IF EXISTS | IF <static_column_conditions> ) ] ;
Syntax legend
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.

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

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.

Examples

Delete data in specified columns from a row

The examples in this section use this table:

CREATE TABLE IF NOT EXISTS cycling.cyclist_name (
  id UUID PRIMARY KEY,
  lastname text,
  firstname text
);
WARNING: cqlsh was built against 5.0-beta1, but this server is 5.0.  All features may not work!

CREATE TABLE cycling.cyclist_name (
    id uuid PRIMARY KEY,
    firstname text,
    lastname text
) WITH additional_write_policy = '99p'
    AND allow_auto_snapshot = true
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND cdc = false
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.UnifiedCompactionStrategy', 'max_sstables_to_compact': '64', 'min_sstable_size': '100MiB', 'scaling_parameters': 'T4', 'sstable_growth': '0.3333333333333333', 'target_sstable_size': '1GiB'}
    AND compression = {'chunk_length_in_kb': '16', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND memtable = 'default'
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND extensions = {}
    AND gc_grace_seconds = 864000
    AND incremental_backups = true
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99p';

Delete the data in specific columns by listing them after the DELETE command, separated by commas. Change the data in first and last name columns to null for the cyclist specified by id.

SELECT * FROM cycling.cyclist_name;
Results 
WARNING: cqlsh was built against 5.0-beta1, but this server is 5.0.  All features may not work!

 id                                   | age  | firstname | lastname
--------------------------------------+------+-----------+-----------------
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 | null |      null |            null
 8566eb59-07df-43b1-a21b-666a3c08c08a |   18 |  Marianne |            DAAE
 fb372533-eb95-4bb4-8685-6ef61e994caa | null |   Michael |        MATTHEWS
 5b6962dd-3f90-4c93-8f61-eabfa4a803e2 |   23 |  Marianne |             VOS
 220844bf-4860-49d6-9a4b-6b5d3a79cbfb | null |     Paolo |       TIRALONGO
 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 | null |    Steven |      KRUIKSWIJK
 e7cd5752-bc0d-4157-a80f-7523add8dbcd | null |      Anna | VAN DER BREGGEN

(7 rows)

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 by specifying IF EXISTS.

DELETE FROM cycling.cyclist_name 
  WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0 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:

WARNING: cqlsh was built against 5.0-beta1, but this server is 5.0.  All features may not work!

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

Delete a row based on a static column condition

The IF condition limits the WHERE clause, allowing selection based on values in non-PRIMARY KEY columns, such as first and last name. This example removes the cyclist record if the first and last names do not match.

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

The results show all the applicable data. The condition was not met because the last names did not match; therefore the operation was not applied.

WARNING: cqlsh was built against 5.0-beta1, but this server is 5.0.  All features may not work!

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

Conditionally deleting data from a column

Use the IF or IF EXISTS clauses to conditionally delete data from columns. Deleting column data conditionally is similar to making an UPDATE conditionally.

Add IF EXISTS to the command to ensure that an operation is not performed if the specified row does not exist:

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

No such row exists so the conditions returns False and the command fails. In this case, standard output looks like:

WARNING: cqlsh was built against 5.0-beta1, but this server is 5.0.  All features may not work!

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

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

If all the conditions return True, the standard output is the same as if IF EXISTS returned True. If any of the conditions fails, standard output displays False in the [applied] column and also displays information about the condition that failed:

WARNING: cqlsh was built against 5.0-beta1, but this server is 5.0.  All features may not work!

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

Deleting one or more rows

The WHERE clause specifies which row or rows to delete from a specified table.

DELETE FROM cycling.cyclist_name WHERE id = 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47;

The cyclist named Steven Kruikswijk was deleted from the table.

WARNING: cqlsh was built against 5.0-beta1, but this server is 5.0.  All features may not work!

 id                                   | age  | firstname | lastname
--------------------------------------+------+-----------+-----------------
 8566eb59-07df-43b1-a21b-666a3c08c08a |   18 |  Marianne |            DAAE
 fb372533-eb95-4bb4-8685-6ef61e994caa | null |   Michael |        MATTHEWS
 5b6962dd-3f90-4c93-8f61-eabfa4a803e2 |   23 |  Marianne |             VOS
 220844bf-4860-49d6-9a4b-6b5d3a79cbfb | null |     Paolo |       TIRALONGO
 e7cd5752-bc0d-4157-a80f-7523add8dbcd | null |      Anna | VAN DER BREGGEN

(5 rows)

To delete more than one row, use the keyword IN and supply a list of comma-separated values in parentheses:

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

Using IN predicates on non-primary keys is not supported.

CQL supports an empty list of values in the IN clause, which is useful in Java Driver applications.

The cyclists Marianne Vos and Paolo Tiralongo were deleted from the table.

WARNING: cqlsh was built against 5.0-beta1, but this server is 5.0.  All features may not work!

 id                                   | age  | firstname | lastname
--------------------------------------+------+-----------+-----------------
 8566eb59-07df-43b1-a21b-666a3c08c08a |   18 |  Marianne |            DAAE
 fb372533-eb95-4bb4-8685-6ef61e994caa | null |   Michael |        MATTHEWS
 e7cd5752-bc0d-4157-a80f-7523add8dbcd | null |      Anna | VAN DER BREGGEN

(3 rows)

Deleting old data using a 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';

Deleting from a collection set, list, or map

To delete all elements from a set for a race, specify the column_name, which is sponsorship:

DELETE sponsorship FROM cycling.race_sponsors 
  WHERE race_name = 'Giro d''Italia';

To delete an 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';

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.

Using an UPDATE command with a subtraction operator to remove a list element in a safer and faster manner is recommended. See List fields.

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

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

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

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: +1 (650) 389-6000, info@datastax.com