Cassandra: Update a record in table

You can update a row in table using UPDATE statement.

Syntax

<update-stmt> ::= UPDATE <tablename>
                  ( USING <option> ( AND <option> )* )?
                  SET <assignment> ( ',' <assignment> )*
                  WHERE <where-clause>
                  ( IF <condition> ( AND condition )* )?

<assignment> ::= <identifier> '=' <term>
               | <identifier> '=' <identifier> ('+' | '-') (<int-term> | <set-literal> | <list-literal>)
               | <identifier> '=' <identifier> '+' <map-literal>
               | <identifier> '[' <term> ']' '=' <term>
               | <identifier> '.' <field> '=' <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>

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

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

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

<option> ::= TIMESTAMP <integer>
           | TTL <integer>

Example

cqlsh> SELECT * FROM cassandratutorial.employee;

 id | age | firstname | lastname
----+-----+-----------+----------
  1 |  30 |   Krishna |   Gurram
  2 |  31 |       Ram |   Gurram
  3 |  45 |     Vijay |   Ponnam

(3 rows)

a. Set the age of employee to 42, where employee id is 1.

UPDATE cassandratutorial.employee SET age=42 where id=1;

cqlsh> UPDATE cassandratutorial.employee SET age=42 where id=1;
cqlsh> 
cqlsh> SELECT * FROM cassandratutorial.employee;

 id | age | firstname | lastname
----+-----+-----------+----------
  1 |  42 |   Krishna |   Gurram
  2 |  31 |       Ram |   Gurram
  3 |  45 |     Vijay |   Ponnam

(3 rows)

You can even update multiple columns in single update statement.

For example, below statement update age and firstname fields of the employee with id=1

UPDATE cassandratutorial.employee SET age=31, firstname='Hari' where id=1;

cqlsh> UPDATE cassandratutorial.employee SET age=31, firstname='Hari' where id=1;
cqlsh> 
cqlsh> SELECT * FROM cassandratutorial.employee;

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

(3 rows)

You can even update multiple rows in single UPDATE query.

UPDATE cassandratutorial.employee SET age=31, firstname='Hari' where id in (1, 3)    

cqlsh> UPDATE cassandratutorial.employee SET age=31, firstname='Hari' where id in (1, 3);
cqlsh> 
cqlsh> SELECT * FROM cassandratutorial.employee;

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

(3 rows)

Previous                                                    Next                                                    Home