Cassandra: DELETE: Delete a row or column from table

You can delete a row from cassandra table using 'DELETE FROM' statement.

Syntax:

<delete-stmt> ::= DELETE ( <selection> ( ',' <selection> )* )?
                  FROM <tablename>
                  ( USING TIMESTAMP <integer>)?
                  WHERE <where-clause>
                  ( IF ( EXISTS | ( <condition> ( AND <condition> )*) ) )?

<selection> ::= <identifier>
              | <identifier> '[' <term> ']'
              | <identifier> '.' <field>

<where-clause> ::= <relation> ( AND <relation> )*

<relation> ::= <identifier> <op> <term>
             | '(' <identifier> (',' <identifier>)* ')' <op> <term-tuple>
             | <identifier> IN '(' ( <term> ( ',' <term>)* )? ')'
             | <identifier> IN <variable>
             | '(' <identifier> (',' <identifier>)* ')' IN '(' ( <term-tuple> ( ',' <term-tuple>)* )? ')'
             | '(' <identifier> (',' <identifier>)* ')' IN <variable>

<op> ::= '=' | '<' | '>' | '<=' | '>='
<in-values> ::= (<variable> | '(' ( <term> ( ',' <term> )* )? ')')

<condition> ::= <identifier> (<op> | '!=') <term>
              | <identifier> IN <in-values>
              | <identifier> '[' <term> ']' (<op> | '!=') <term>
              | <identifier> '[' <term> ']' IN <in-values>
              | <identifier> '.' <field> (<op> | '!=') <term>
              | <identifier> '.' <field> IN <in-values>

cqlsh> SELECT * FROM cassandratutorial.employee;

 id | age | firstname | lastname
----+-----+-----------+----------
  1 |  31 |      Hari |   Gurram
  2 |  31 |       Ram |   Gurram
  3 |  33 |       Ram |   Ponnam

Delete a row from table employee

cqlsh> SELECT * FROM cassandratutorial.employee;

 id | age | firstname | lastname
----+-----+-----------+----------
  1 |  31 |      Hari |   Gurram
  2 |  31 |       Ram |   Gurram
  3 |  33 |       Ram |   Ponnam

(3 rows)
cqlsh> DELETE FROM cassandratutorial.employee WHERE id=2;
cqlsh> 
cqlsh> SELECT * FROM cassandratutorial.employee;

 id | age | firstname | lastname
----+-----+-----------+----------
  1 |  31 |      Hari |   Gurram
  3 |  33 |       Ram |   Ponnam

(2 rows)

Delete a column from table employee

DELETE age from cassandratutorial.employee WHERE id=3;

Above statement deletes the column age from the employee table for the id=3.

cqlsh> SELECT * FROM cassandratutorial.employee;

 id | age | firstname | lastname
----+-----+-----------+----------
  1 |  31 |      Hari |   Gurram
  3 |  33 |       Ram |   Ponnam

(2 rows)
cqlsh> 
cqlsh> DELETE age from cassandratutorial.employee WHERE id=3;
cqlsh> 
cqlsh> SELECT * FROM cassandratutorial.employee;

 id | age  | firstname | lastname
----+------+-----------+----------
  1 |   31 |      Hari |   Gurram
  3 | null |       Ram |   Ponnam

(2 rows)

You can also delete particular column by updating its value to null. For example, below statement deletes lastname column for the employee with id=1.

UPDATE cassandratutorial.employee SET lastname=null WHERE id=1;

cqlsh> SELECT * FROM cassandratutorial.employee;

 id | age  | firstname | lastname
----+------+-----------+----------
  1 |   31 |      Hari |   Gurram
  3 | null |       Ram |   Ponnam

(2 rows)
cqlsh> UPDATE cassandratutorial.employee SET lastname=null WHERE id=1;
cqlsh> 
cqlsh> SELECT * FROM cassandratutorial.employee;

 id | age  | firstname | lastname
----+------+-----------+----------
  1 |   31 |      Hari |     null
  3 | null |       Ram |   Ponnam

(2 rows)

You can even perform the same using INSERT statement.

cqlsh> SELECT * FROM cassandratutorial.employee;

 id | age  | firstname | lastname
----+------+-----------+----------
  1 |   31 |      Hari |     null
  3 | null |       Ram |   Ponnam

(2 rows)
cqlsh> 
cqlsh> INSERT INTO cassandratutorial.employee JSON '{"id" : 4, "firstName" : "Chitra", "lastName" : null, "age" : 45}';
cqlsh> 
cqlsh> SELECT * FROM cassandratutorial.employee;

 id | age  | firstname | lastname
----+------+-----------+----------
  1 |   31 |      Hari |     null
  4 |   45 |    Chitra |     null
  3 | null |       Ram |   Ponnam

(3 rows)

Previous                                                    Next                                                    Home