ScyllaDB Docs ScyllaDB Open Source CQL Reference Data Manipulation DELETE

DELETE¶

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¶

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¶

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': '3'}  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

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?