UPDATE

Update columns in a row.

Description

UPDATE writes one or more column values to a row in a Cassandra table. Like INSERT, UPDATE is an upsert operation: if the specified row does not exist, the command creates it. All UPDATEs within the same partition key are applied atomically and in isolation.

The USING clause can add a time to live (TTL) value to the row. You cannot apply TTLs to counter columns.

The WHERE clause specifies the row or rows to be updated. To specify a row, the WHERE clause must provide a value for each column of the row's primary key. To specify more than one row, you can use the IN keyword to introduce a list of possible values. You can only do this for the last column of the primary key.

UPDATE cycling.cyclist_name
SET comments ='='Rides hard, gets along with others, a real winner'
WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa IF EXISTS;

The UPDATE command does not return any result unless it includes IF EXISTS.

Parameters

keyspace_name

The name of the keyspace containing the table to be updated. Not needed if the keyspace has been set for the session with the USE command.

table_name

The name of the table to be updated.

time_value

The value for TTL is a number of seconds. Column values in a command marked with TTL are automatically marked as deleted (with a tombstone) after the specified number of seconds. The TTL applies to the marked column values, not the column itself. Any subsequent update of the column resets the value to the TTL specified in the update. By default, values never expire. You cannot set a time_value for data in a counter column.

You can set a default TTL for an entire table by setting the table's default_time_to_live property. If you try to set a TTL for a specific column that is longer than the time defined by the table TTL, Cassandra returns an error.

In addition, you can delete a column's TTL by setting its time_value to zero.

timestamp_value

If TIMESTAMP is used, the inserted column is marked with its value – a timestamp in microseconds. If a TIMESTAMP value is not set, Cassandra uses the time (in microseconds) that the update occurred to the column.

assignment

Assigns a value to an existing element. Can be one of:

column_name = column_value [, column_name = column_value] . . . 
| counter_column_name = counter_column_name + | - counter_offset
| list_name = ['list_item' [, 'list_item'] . . . ]
| list_name = list_name + | - ['list_item' [, 'list_item'] . . . ] 
| list_name = ['list_item' [, 'list_item'] . . . ] + list_name  
| map_name = map_name + | - { map_key : map_value [, map_key : map_value . . . }
| map_name[ index ] = map_value
| set_name = set_name + | - { ['set_item'] } 

Variable	Description
column_name	The name of the column to be updated.
column_value	The value to be inserted for the specified column name.
counter_column_name	The name of the counter column to be updated.
counter_offset	The value by which the specified counter is be incremented or decremented (depending on whether the counter_offset is preceded by "=" or "-").
list_name	The name of the list to be updated. Format of a list: [list_item , list_item , list_item] Note the use of square brackets.
list_item	The value to be added to the list, or removed from it.
map_name	The name of the map to be updated. Format of a map: { key : value , key: value , key: value . . . } Note the use of curly brackets ( { } ).
map_key	The first term or keyin a map entry.
map_value	The second term or value in a map entry.
set_name	The name of the set to be updated. Format of a set: { set_item , set_item , set_item . . . } Note the use of curly brackets ( { } ).
set_item	The literal value included in a set. Note: The difference between a list and a set: each item in a set must be unique.

row_specification

The WHERE clause must identify the row or rows to be updated by primary key:

• To specify one row, use primary_key_name = primary_key_value. If the primary key is a combination of elements, follow this with AND primary_key_name = primary_key_value .... The WHERE clause must specify a value for every component of the primary key.
• To specify more than one row, use primary_key_name IN ( primary_key_value, primary_key_value … ). This only works for the last component of the primary key.

Note: To update a static column, you only need to specify the partition key.

IF EXISTS / IF NOT EXISTS | IF condition

An IF clause can limit the command's action on rows that match the WHERE clause:

• Use IF EXISTS to make the UPDATE fail when rows match the WHERE conditions.
• Use IF NOT EXISTS to make the UPDATE fail when no rows exist match the WHERE conditions.
• Use IF to specify one or more conditions that must test true for the values in the specified row or rows.

When an IF is used, the command returns a result to standard output. For examples, see Conditionally updating columns.

Examples: 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 cycling.cyclists
  SET firstname = 'Marianne,
  lastname = 'VOS'
  WHERE id = 88b8fd18-b1ed-4e96-bf79-4280797cba80;

Update a row in a table with a complex primary key:

To do this, 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;

Updating a counter column

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

UPDATE cycling.popular_count SET popularity = popularity + 2 WHERE id = 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47;

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

Creating a partition using UPDATE

UPDATE cycling.cyclists
SET firstname = 'Anna', lastname = 'VAN DER BREGGEN' WHERE id = e7cd5752-bc0d-4157-a80f-7523add8dbcd;

Updating a list

To insert values into the list:

UPDATE cycling.upcoming_calendar 
SET events = ['Criterium du Dauphine','Tour de Suisse'];

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

UPDATE cycling.upcoming_calendar 
SET events = ['Tour de France'] + events WHERE year=2015 AND month=06;

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

UPDATE cycling.upcoming_calendar 
SET events = events + ['Tour de France'] WHERE year=2017 AND month=05;

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

UPDATE cycling.upcoming_calendar 
SET events[4] = 'Tour de France' WHERE year=2016 AND month=07;

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

UPDATE cycling.upcoming_calendar 
SET events = events - ['Criterium du Dauphine'] WHERE year=2016 AND month=07;

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."

Updating a set

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

UPDATE cycling.cyclist_career_teams
SET teams = teams + {'Team DSB - Ballast Nedam'} WHERE id=5b6962dd-3f90-4c93-8f61-eabfa4a803e2;

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

UPDATE cycling.cyclist_career_teams
SET teams = teams - {'DSB Bank Nederland bloeit'} WHERE id=5b6962dd-3f90-4c93-8f61-eabfa4a803e2;

To remove all elements from a set:

UPDATE cycling.cyclist_career_teams
SET teams = {} WHERE id=5b6962dd-3f90-4c93-8f61-eabfa4a803e2;

Updating a map

To set or replace map data, enclose the values in map syntax: strings in curly brackets, separated by a colon.

UPDATE cycling.upcoming_calendar 
SET description = description + {'Criterium du Dauphine' : 'Easy race'} WHERE year = 2015;

To update or set a specific element, such as adding a new race to the calendar in a map named events:

UPDATE cycling.upcoming_calendar 
SET events[2] = 'Vuelta Ciclista a Venezuela' WHERE year = 2016 AND month = 06;

To set the a TTL for each map element:

UPDATE cycling.upcoming_calendar USING TTL <ttl_value>
SET events[2] = 'Vuelta Ciclista a Venezuela' WHERE year = 2016 AND month = 06;

You can update the map by adding one or more elements separated by commas:

UPDATE cycling.upcoming_calendar 
SET description = description + {'Criterium du Dauphine' : 'Easy race', 'Tour du Suisse' : 'Hard uphill race'}
WHERE year = 2015 AND month = 6;

Remove elements from a map in the same way using - instead of +.

About updating sets and maps caution

CQL supports alternate methods for updating sets and maps. These alternatives may seem to accomplish the same tasks, but Cassandra handles them differently in important ways.

UPDATE cycling.upcoming_calendar 
SET description = 
{'Criterium du Dauphine' : 'Easy race', 
 'Tour du Suisse' : 'Hard uphill race'} 
WHERE year = 2015 AND month = 6;

UPDATE cycling.upcoming_calendar 
SET description = description + { 'Tour de France' : 'Very competitive'} 
WHERE year = 2015 AND month = 6;

UPDATE cycling.upcoming_calendar 
SET description = 
{'Criterium du Dauphine' : 'Easy race', 
 'Tour du Suisse' : 'Hard uphill race',
 'Tour de France' : 'Very competitive'} 
WHERE year = 2015 AND month = 6;

These two statements seem to do the same thing. But behind the scenes, Cassandra processes the second statement by deleting the entire collection and replacing it with a new collection containing three entries. This creates tombstones for the deleted entries, even though these entries are identical to the entries in the new map collection. If your code updates all map collections this way, it generates many tombstones, which may slow the system down.

The examples above use map collections, but the same caution applies to updating sets.

Updating a UDT with non-collection fields

In Cassandra 3.6 and later, to change the value of an individual field value in a user-defined type with non-collection fields, use the UPDATE command:

UPDATE cyclist_stats SET basics.birthday = '2000-12-12' WHERE id = 220844bf-4860-49d6-9a4b-6b5d3a79cbfb;

Conditionally updating columns

In Cassandra 2.0.7 and later, 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 exists:

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.

Performing conditional updates in a BATCH

The UPDATE command can create new rows. But until Cassandra 3.0, the new updated rows were not immediately available for lightweight transactions (for example, conditional statements added to UPDATE commands) if both the UPDATE and the conditional statement were applied in the same BATCH.

For example, consider a simple table with four defined columns:

CREATE TABLE mytable (a int, b int, s int static, d text, PRIMARY KEY (a, b))

BEGIN BATCH
     INSERT INTO mytable (a, b, d) values (7, 7, 'a')
     UPDATE mytable SET s = 7 WHERE a = 7 IF s = NULL;
APPLY BATCH

In the first batch above, the insert command creates a partition with primary key values (7,7) but does not set a value for the s column. Even though the s column was not defined for this row, the IF s = NULL conditional succeeds, so the batch succeeds. (In previous versions of Cassandra, the conditional would have failed,and that failure would have caused the entire batch to fail.)

The second batch demonstrates more complex handling of a lightweight transaction:

BEGIN BATCH
     INSERT INTO mytable (a, b, d) values (7, 7, 'a')
     UPDATE mytable SET s = 1 WHERE a = 1 IF s = NULL;
APPLY BATCH

In this case, the IF statement tests the value of a column in a partition that does not even exist before it is created by the UPDATE. Even so, Cassandra recognizes the implicit presence of the partition and its column s, and lets the conditional test succeed. This allows the batch to succeed.