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 |
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
Syntax conventions | Description |
---|---|
UPPERCASE |
Literal keyword. |
Lowercase |
Not literal. |
|
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. |
- 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
);
CREATE TABLE cycling.cyclist_name (
id uuid PRIMARY KEY,
firstname text,
lastname text
) WITH additional_write_policy = '99PERCENTILE'
AND bloom_filter_fp_chance = 0.01
AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
AND comment = ''
AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
AND crc_check_chance = 1.0
AND default_time_to_live = 0
AND gc_grace_seconds = 864000
AND max_index_interval = 2048
AND memtable_flush_period_in_ms = 0
AND min_index_interval = 128
AND nodesync = {'enabled': 'true', 'incremental': 'true'}
AND read_repair = 'BLOCKING'
AND speculative_retry = '99PERCENTILE';
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
id | firstname | lastname
--------------------------------------+-----------+-----------------
e7ae5cf3-d358-4d99-b900-85902fda9bb0 | null | null
fb372533-eb95-4bb4-8685-6ef61e994caa | Michael | MATTHEWS
5b6962dd-3f90-4c93-8f61-eabfa4a803e2 | Marianne | VOS
220844bf-4860-49d6-9a4b-6b5d3a79cbfb | Paolo | TIRALONGO
6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 | Steven | KRUIKSWIJK
e7cd5752-bc0d-4157-a80f-7523add8dbcd | Anna | VAN DER BREGGEN
(6 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:
[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.
[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:
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:
InvalidRequest: Error from server: code=2200 [Invalid query]
message="PRIMARY KEY column age cannot have IF conditions
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.
id | firstname | lastname
--------------------------------------+-----------+-----------------
fb372533-eb95-4bb4-8685-6ef61e994caa | Michael | MATTHEWS
5b6962dd-3f90-4c93-8f61-eabfa4a803e2 | Marianne | VOS
220844bf-4860-49d6-9a4b-6b5d3a79cbfb | Paolo | TIRALONGO
e7cd5752-bc0d-4157-a80f-7523add8dbcd | Anna | VAN DER BREGGEN
(4 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 |
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.
id | firstname | lastname
--------------------------------------+-----------+-----------------
fb372533-eb95-4bb4-8685-6ef61e994caa | Michael | MATTHEWS
e7cd5752-bc0d-4157-a80f-7523add8dbcd | Anna | VAN DER BREGGEN
(2 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 |
Using an |
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;