DELETE
Removes data from one or more columns or removes the entire row.
DELETE
can impact performance. Tombstones are created, causing stale data to be read until
removed by a compaction.Synopsis
DELETE [ column_name [ term ] [ , ... ] ] FROM [keyspace_name.]table_name [ USING TIMESTAMP timestamp_value ] WHERE PK_column_conditions [ ( IF EXISTS | IF static_column_conditions ) ] ;
Syntax conventions | Description |
---|---|
UPPERCASE | Literal keyword. |
Lowercase | Not literal. |
Italics |
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. |
- 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
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
.
DELETE firstname, lastname FROM cycling.cyclist_name WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0;
CREATE TABLE cycling.cyclist_name ( id UUID PRIMARY KEY, lastname text, firstname text );
id | firstname | lastname
--------------------------------------+-----------+-----------------
e7ae5cf3-d358-4d99-b900-85902fda9bb0 | Alex | FRAME
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)
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;
True
(if a row with this primary
key does exist), standard output displays a table like the following:
[applied]
-----------
True
id | firstname | lastname
--------------------------------------+-----------+-----------------
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
(5 rows)
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';
[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.
DELETE id FROM cycling.cyclist_id WHERE lastname = 'JONES' and firstname = 'Bram' IF EXISTS;
False
and the command
fails. In this case, standard output looks
like: [applied]
-----------
False
DELETE id FROM cycling.cyclist_id WHERE lastname = 'WELTEN' AND firstname = 'Bram' IF age = 20;
False
in the [applied]
column and also displays
information about the condition that
failed: [applied] | age
-----------+-----
False | 18
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;
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 );
IN
predicates on non-primary keys is not supported. 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
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' ;
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.UPDATE
command with a subtraction operator to
remove a list element in a safer and faster manner is recommended. See List fields.DELETE teams[2014] FROM cycling.cyclist_teams WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;