UPDATE¶

Synopsis¶

UPDATE keyspace_name.table_name
USING TTL seconds
SET assignment , assignment, ...
WHERE row_specification

assignment is one of:

column_name = value

set_or_list_item «+ | - »«set | list »
map_name + map

collection_column_name [ term ] = value

counter_column_name = counter_column_name «+ | - «integer

set is:

{ item1, item2, . . . }

list is:

[ name, value ]

map is:

{ name : value, name : value, . . . }

term is:

[ list_index_position | [ list_value ]

row_specification is:

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

Synopsis legend

Description¶

An UPDATE writes one or more column values to existing columns in a Cassandra table. No results are returned. A statement begins with the UPDATE keyword followed by a Cassandra table name. To update multiple columns, separate the name-value pairs using commas.

The SET clause specifies the column name-value pairs to update. Separate multiple name-value pairs using commas. If the named column exists, its value is updated. If the column does not exist, use ALTER TABLE to create the new column.

To update a counter column value in a counter table, specify a value to increment or decrement value the current value of the counter column.

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

In an UPDATE statement, you can specify these options:

• TTL seconds
• Timestamp

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 in one of the following formats:

• A string in ISO 8601 format
• 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 composite 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;

Examples¶

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;

Update the value of a counter column:

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

Using 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';

Using 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['2012-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';

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';