UPDATE

Update columns in a row.

Synopsis

UPDATE keyspace_name.table_name
USING option AND option
SET assignment, assignment, ...
WHERE row_specification 
IF column_name = literal AND column_name = literal . . . 
IF EXISTS

option is one of:

• TIMESTAMP microseconds
• TTL seconds

assignment is one of:

column_name = value
set_or_list_item = set_or_list_item  + | -  ...
map_name = map_name  + | -  ...
map_name = map_name  + | -  { map_key : map_value, ... } 
column_name [ term ] = value
counter_column_name = counter_column_name  + | -  integer

set is:

{ literal, literal, . . . }

list is:

[ literal, literal ]

map is:

{ literal : literal, literal : literal, . . . }

term is:

[ list_index_position | [ key_value ]

row_specification is:

primary key name = key_value
primary key name IN (key_value ,...)

Description

An UPDATE writes one or more column values for a given row to a Cassandra table. No results are returned. A statement begins with the UPDATE keyword followed by a Cassandra table name.

The row is created if none existed before, and updated otherwise. Specify the row to update in the WHERE clause by including all columns composing the partition key. The IN relation is supported only for the last column of the partition key. The UPDATE SET operation is not valid on a primary key field. Specify other column values using SET. To update multiple columns, separate the name-value pairs using commas.

UPDATE customer_account
SET customer_email='lauras@gmail.com'
If customer_email='laurass@gmail.com';

To update a counter column value in a counter table, specify the increment or decrement to the current value of the counter column. Unlike the INSERT command, the UPDATE command supports counters. Otherwise, the update and insert operations are identical.

UPDATE UserActionCounts SET total = total + 2 WHERE keyalias = 523;

In an UPDATE statement, you can specify these options on columns that are not counter columns:

• TTL seconds
• Timestamp microseconds

TTL input is in seconds. TTL column values are automatically marked as deleted (with a tombstone) after the requested amount of time has expired. TTL marks the inserted values, not the column itself, for expiration. Any subsequent update of the column resets the TTL to the TTL specified in the update. By default, values never expire.

The TIMESTAMP input is an integer representing microseconds. If not specified, the time (in microseconds) that the write occurred to the column is used. Each update statement requires a precise set of primary keys to be specified using a WHERE clause. You need to specify all keys in a table having compound and clustering columns. For example, update the value of a column in a table having a compound primary key, userid and url:

UPDATE excelsior.clicks USING TTL 432000
  SET user_name = 'bob'
  WHERE userid=cfd66ccc-d857-4e90-b1e5-df98a3d40cd6 AND
    url='http://google.com';
UPDATE Movies SET col1 = val1, col2 = val2 WHERE movieID = key1;
UPDATE Movies SET col3 = val3 WHERE movieID IN (key1, key2, key3);
UPDATE Movies SET col4 = 22 WHERE movieID = key4;

CQL supports an empty list of values in the IN clause, useful in Java Driver applications when passing empty arrays as arguments for the IN clause.

Examples of updating a column

Update a column in several rows at once:

UPDATE users
  SET state = 'TX'
  WHERE user_uuid
  IN (88b8fd18-b1ed-4e96-bf79-4280797cba80,
    06a8913c-c0d6-477c-937d-6c1b69a95d43,
    bc108776-7cb5-477f-917d-869c12dfffa8);

Update several columns in a single row:

UPDATE users
  SET name = 'John Smith',
  email = 'jsmith@cassie.com'
  WHERE user_uuid = 88b8fd18-b1ed-4e96-bf79-4280797cba80;

Updating a counter column

You can increase or decrease the value of a counter column by an arbitrary numeric value though the assignment of an expression that adds or substracts the value. To update the value of a counter column, use the syntax shown in the following example:

UPDATE counterks.page_view_counts
  SET counter_value = counter_value + 2
  WHERE url_name='www.datastax.com' AND page_name='home';

To use a lightweight transaction on a counter column to ensure accuracy, put one or more counter updates in the batch statement.

Updating a collection set

To add an element to a set, use the UPDATE command and the addition (+) operator:

UPDATE users
  SET emails = emails + {'fb@friendsofmordor.org'} WHERE user_id = 'frodo';

To remove an element from a set, use the subtraction (-) operator.

UPDATE users
  SET emails = emails - {'fb@friendsofmordor.org'} WHERE user_id = 'frodo';

To remove all elements from a set, you can use the UPDATE statement:

UPDATE users SET emails = {} WHERE user_id = 'frodo';

Updating a collection map

To set or replace map data, you can use the UPDATE command. Enclose the timestamp and text values in map collection syntax: strings in curly brackets, separated by a colon.

UPDATE users
  SET todo = { '2012-9-24' : 'enter mordor', 
    '2012-10-2 12:00' : 'throw ring into mount doom' }
  WHERE user_id = 'frodo';

You can also update or set a specific element using the UPDATE command. For example, update a map named todo to insert a reminder, 'die' on October 2 for user frodo.

UPDATE users SET todo['2014-10-2 12:10'] = 'die'
  WHERE user_id = 'frodo';

You can set the a TTL for each map element:

UPDATE users USING TTL <ttl value>
 SET todo['2012-10-1'] = 'find water' WHERE user_id = 'frodo';

In Cassandra 2.1.1 and later, you can update the map by adding one or more elements separated by commas:

UPDATE users SET todo = todo + { '2012-10-1': 'find water', '2014-12-15': 'buy presents' } where user_id = 'frodo';

You can remove elements from a map in the same way using - instead of +.

Using a collection list

To insert values into the list.

UPDATE users
  SET top_places = [ 'rivendell', 'rohan' ] WHERE user_id = 'frodo';

To prepend an element to the list, enclose it in square brackets, and use the addition (+) operator:

UPDATE users
  SET top_places = [ 'the shire' ] + top_places WHERE user_id = 'frodo';

To append an element to the list, switch the order of the new element data and the list name in the UPDATE command:

UPDATE users
  SET top_places = top_places + [ 'mordor' ] WHERE user_id = 'frodo';

To add an element at a particular position, use the list index position in square brackets:

UPDATE users SET top_places[2] = 'riddermark' WHERE user_id = 'frodo';

To remove all elements having a particular value, use the UPDATE command, the subtraction operator (-), and the list value in square brackets:

UPDATE users
  SET top_places = top_places - ['riddermark'] WHERE user_id = 'frodo';

To update data in a collection column of a user-defined type, enclose components of the type in parentheses within the curly brackets, as shown in "Using a user-defined type."

Conditionally updating columns

You can conditionally update columns using IF or IF EXISTS.

Add IF EXISTS to the command to ensure that the operation is not performed if the specified row does not exist:

UPDATE cycling.cyclist_id SET age = 28 WHERE lastname = 'WELTEN' and firstname = 'Bram' IF EXISTS;

Without IF EXISTS, the command proceeds with no standard output. If IF EXISTS returns true (if a row with this primary key does exist), standard output displays a table like the following:

If no such row exists, however, the condition returns FALSE and the command fails. In this case, standard output looks like:

UPDATE cyclist_id SET id = 15a116fc-b833-4da6-ab9a-4a3775750239 where lastname = 'WELTEN' and firstname = 'Bram' IF age = 18;

Conditional updates are examples of "lightweight transactions." They incur a non-negligible performance cost and should be used sparingly.