DELETE statement [YCQL]

Synopsis

Use the DELETE statement to remove rows from a specified table that meet a given condition.

Syntax

Diagram

Grammar

ebnf

delete ::= DELETE FROM table_name
               [ USING TIMESTAMP timestamp_expression ] WHERE
               where_expression [ IF { [ NOT ] EXISTS | if_expression } ]
               [ RETURNS STATUS AS ROW ]

Where

table_name is an identifier (possibly qualified with a keyspace name).
Restrictions on where_expression and if_expression are covered in the Semantics section.
See Expressions for more information on syntax rules.

Semantics

An error is raised if the specified table_name does not exist.
The where_expression and if_expression must evaluate to boolean values.
The USING TIMESTAMP clause indicates you would like to perform the DELETE as if it was done at the timestamp provided by the user. The timestamp is the number of microseconds since epoch.
Note: You should either use the USING TIMESTAMP clause in all of your statements or none of them. Using a mix of statements where some have USING TIMESTAMP and others do not will lead to very confusing results.
DELETE is always done at QUORUM consistency level irrespective of setting.

WHERE Clause

The where_expression must specify conditions for all primary-key columns.
The where_expression must not specify conditions for any regular columns.
The where_expression can only apply AND and = operators. Other operators are not yet supported.

IF Clause

The if_expression can only apply to non-key columns (regular columns).
The if_expression can contain any logical and boolean operators.
Deleting only some column values from a row is not yet supported.
IF EXISTS and IF NOT EXISTS options are mostly for symmetry with the INSERT and UPDATE commands.
- IF EXISTS works like a normal delete but additionally returns whether the delete was applied (a row was found with that primary key).
- IF NOT EXISTS is effectively a no-op since rows that do not exist cannot be deleted (but returns whether no row was found with that primary key).

`USING` Clause

The timestamp_expression must be an integer value (or a bind variable marker for prepared statements).

Examples

Delete a row from a table

sql

ycqlsh:example> CREATE TABLE employees(department_id INT,
                                      employee_id INT,
                                      name TEXT,
                                      PRIMARY KEY(department_id, employee_id));

sql

ycqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 1, 'John');

sql

ycqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 2, 'Jane');

sql

ycqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (2, 1, 'Joe');

sql

ycqlsh:example> SELECT * FROM employees;

output

 department_id | employee_id | name
---------------+-------------+------
             1 |           1 | John
             1 |           2 | Jane
             2 |           1 |  Joe

Delete statements identify rows by the primary key columns.

sql

ycqlsh:example> DELETE FROM employees WHERE department_id = 1 AND employee_id = 1;

Deletes on non-existent rows are no-ops.

sql

ycqlsh:example> DELETE FROM employees WHERE department_id = 3 AND employee_id = 1;

sql

ycqlsh:example> SELECT * FROM employees;

output

 department_id | employee_id | name
---------------+-------------+------
             1 |           2 | Jane
             2 |           1 |  Joe

Conditional delete using the `IF` clause

'IF' clause conditions will return whether they were applied or not.

sql

ycqlsh:example> DELETE FROM employees WHERE department_id = 2 AND employee_id = 1 IF name = 'Joe';

output

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

sql

ycqlsh:example> DELETE FROM employees WHERE department_id = 3 AND employee_id = 1 IF EXISTS;

output

 [applied]
-----------
     False

sql

ycqlsh:example> SELECT * FROM employees;

output

 department_id | employee_id | name
---------------+-------------+------
             1 |           2 | Jane

Delete several rows with the same partition key

sql

ycqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 1, 'John');

sql

ycqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (2, 1, 'Joe');

sql

ycqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (2, 2, 'Jack');

sql

ycqlsh:example> SELECT * FROM employees;

output

 department_id | employee_id | name
---------------+-------------+------
             1 |           1 | John
             1 |           2 | Jane
             2 |           1 |  Joe
             2 |           2 | Jack

Delete all entries for a partition key.

sql

ycqlsh:example> DELETE FROM employees WHERE department_id = 1;

sql

ycqlsh:example> SELECT * FROM employees;

output

 department_id | employee_id | name
---------------+-------------+------
             2 |           1 |  Joe
             2 |           2 | Jack

Delete a range of entries within a partition key.

sql

ycqlsh:example> DELETE FROM employees WHERE department_id = 2 AND employee_id >= 2 AND employee_id < 4;

sql

ycqlsh:example> SELECT * FROM employees;

output

 department_id | employee_id | name
---------------+-------------+------
             2 |           1 |  Joe

Delete with the `USING TIMESTAMP` clause

You can do this as follows:

sql

ycqlsh:foo> INSERT INTO employees(department_id, employee_id, name) VALUES (4, 4, 'Ted') USING TIMESTAMP 1000;

sql

ycqlsh:foo> SELECT * FROM employees;

output

 department_id | employee_id | name
---------------+-------------+------
             4 |           4 |  Ted
             2 |           1 |  Joe

(2 rows)

sql

ycqlsh:foo> DELETE FROM employees USING TIMESTAMP 500 WHERE department_id = 4 AND employee_id = 4;

Not applied since timestamp is lower than 1000

sql

ycqlsh:foo> SELECT * FROM employees;

output

 department_id | employee_id | name
---------------+-------------+------
             4 |           4 |  Ted
             2 |           1 |  Joe

(2 rows)

sql

ycqlsh:foo> DELETE FROM employees USING TIMESTAMP 1500 WHERE department_id = 4 AND employee_id = 4;

Applied since timestamp is higher than 1000.

sql

ycqlsh:foo> SELECT * FROM employees;

output

 department_id | employee_id | name
---------------+-------------+------
             2 |           1 |  Joe

(1 rows)

RETURNS STATUS AS ROW

When executing a batch in YCQL, the protocol returns only one error or return status. The RETURNS STATUS AS ROW feature addresses this limitation and adds a status row for each statement.

See examples in batch docs.

Synopsis

Syntax

Diagram

Grammar

Semantics

WHERE Clause

IF Clause

USING Clause

Examples

Delete a row from a table

Conditional delete using the IF clause

Delete several rows with the same partition key

Delete with the USING TIMESTAMP clause

RETURNS STATUS AS ROW

See also

`USING` Clause

Conditional delete using the `IF` clause

Delete with the `USING TIMESTAMP` clause