## 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](..#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](../type_bool) 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`](../dml_insert) and [`UPDATE`](../dml_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](../batch#row-status). ## See also - [`CREATE TABLE`](../ddl_create_table) - [`INSERT`](../dml_insert) - [`SELECT`](../dml_select/) - [`UPDATE`](../dml_update/) - [`TRUNCATE`](../dml_truncate) - [`Expression`](..#expressions)