Was this page helpful?
Deleting rows or parts of rows uses the DELETE
statement:
delete_statement: DELETE [ `simple_selection` ( ',' `simple_selection` ) ]
: FROM `table_name`
: [ USING `update_parameter` ( AND `update_parameter` )* ]
: WHERE `where_clause`
: [ IF ( EXISTS | `condition` ( AND `condition` )* ) ]
For instance:
DELETE FROM NerdMovies USING TIMESTAMP 1240003134000000
WHERE movie = 'Serenity';
DELETE phone FROM Users
WHERE userid IN (C73DE1D3-AF08-40F3-B124-3FF3E5109F22, B70DE1D0-9908-4AE3-BE34-5573E5B09F14);
The DELETE
statement deletes columns and rows. If column names are provided directly after the DELETE
keyword,
only those columns are deleted from the row indicated by the WHERE
clause. Otherwise, whole rows are removed.
The WHERE
clause specifies which rows are to be deleted. Multiple rows may be deleted with one statement by using an
IN
operator. A range of rows may be deleted using an inequality operator (such as >=
).
DELETE
supports the TIMESTAMP
option with the same semantics as the TIMESTAMP parameter used in the UPDATE
statement.
The DELETE
statement deletes data written with INSERT or UPDATE (or BATCH)
using a timestamp that is less than or equal to the DELETE
timestamp.
For more information on the update_parameter
refer to the UPDATE section.
In a DELETE
statement, all deletions within the same partition key are applied atomically,
meaning either all columns mentioned in the statement are deleted or none.
If DELETE
statement has the same timestamp as INSERT
or
UPDATE
of the same primary key, delete operation prevails (see update ordering).
A DELETE
operation can be conditional through the use of an IF
clause, similar to UPDATE
and INSERT
statements. Each such DELETE
gets a globally unique timestamp.
However, as with INSERT
and UPDATE
statements, this will incur a non-negligible performance cost
(internally, Paxos will be used) and so should be used sparingly.
Please refer to the update parameters section for more information on the update_parameter
.
Range deletions allow you to delete rows from a single partition, given that the clustering key is within the given range. The delete request can be determined on both ends, or it can be open-ended.
Open range deletions delete rows based on an open-ended request (>, <, >=, =<, etc.)
For example, suppose your data set for events at Madison Square Garden includes:
CREATE KEYSPACE mykeyspace WITH replication = {'class': 'NetworkTopologyStrategy', 'replication_factor': '1'} AND durable_writes = true;
use mykeyspace ;
CREATE TABLE events ( id text, created_at date, content text, PRIMARY KEY (id, created_at) );
INSERT into events (id, created_at, content) VALUES ('concert', '2019-11-19', 'SuperM');
INSERT into events (id, created_at, content) VALUES ('concert', '2019-11-15', 'Billy Joel');
INSERT into events (id, created_at, content) VALUES ('game', '2019-11-03', 'Knicks v Sacramento');
INSERT into events (id, created_at, content) VALUES ('concert', '2019-10-31', 'Dead & Company');
INSERT into events (id, created_at, content) VALUES ('game', '2019-10-28', 'Knicks v Chicago');
INSERT into events (id, created_at, content) VALUES ('concert', '2019-10-25', 'Billy Joel');
SELECT * from events;
id | created_at | content
---------+------------+---------------------
game | 2019-10-28 | Knicks v Chicago
game | 2019-11-03 | Knicks v Sacramento
concert | 2019-10-25 | Billy Joel
concert | 2019-10-31 | Dead & Company
concert | 2019-11-15 | Billy Joel
concert | 2019-11-19 | SuperM
(6 rows)
And you wanted to delete all of the concerts from the month of October using an open-ended range delete. You would run:
DELETE FROM events WHERE id='concert' AND created_at <= '2019-10-31';
SELECT * from events;
id | created_at | content
---------+------------+---------------------
game | 2019-10-28 | Knicks v Chicago
game | 2019-11-03 | Knicks v Sacramento
concert | 2019-11-15 | Billy Joel
concert | 2019-11-19 | SuperM
(4 rows)
Apache Cassandra Query Language (CQL) Reference
Copyright
© 2016, The Apache Software Foundation.
Apache®, Apache Cassandra®, Cassandra®, the Apache feather logo and the Apache Cassandra® Eye logo are either registered trademarks or trademarks of the Apache Software Foundation in the United States and/or other countries. No endorsement by The Apache Software Foundation is implied by the use of these marks.
Was this page helpful?
On this page