UPDATE

Modifies one or more column values to a row in a table.

Modifies one or more column values to a row in a table.

Synopsis

UPDATE [keyspace_name.]table_name
  [ USING TTL time_value ]
  [ [ AND ] USING TIMESTAMP timestamp_value ]
  SET assignment [ , assignment , ... ]
  WHERE row_specification
  [ ( IF EXISTS | IF NOT EXISTS | IF condition [ AND condition ... ] ) ] ;
Note: To modify a base table that has a materialized view (MV) using an INSERT or UPDATE command if access permissions are enabled, a user must be granted MODIFY or ALL PERMISSIONS on the base table.
Table 1. Legend
Syntax conventions Description
UPPERCASE Literal keyword.
Lowercase Not literal.
Italics Variable value. Replace with a user-defined value.
[] Optional. Square brackets ( [] ) surround optional command arguments. Do not type the square brackets.
( ) Group. Parentheses ( ( ) ) identify a group to choose from. Do not type the parentheses.
| Or. A vertical bar ( | ) separates alternative elements. Type any one of the elements. Do not type the vertical bar.
... Repeatable. An ellipsis ( ... ) indicates that you can repeat the syntax element as often as required.
'Literal string' Single quotation ( ' ) marks must surround literal strings in CQL statements. Use single quotation marks to preserve upper case.
{ key : value } Map collection. Braces ( { } ) enclose map collections or key value pairs. A colon separates the key and the value.
<datatype1,datatype2> Set, list, map, or tuple. Angle brackets ( < > ) enclose data types in a set, list, map, or tuple. Separate the data types with a comma.
cql_statement; End CQL statement. A semicolon ( ; ) terminates all CQL statements.
[--] Separate the command line options from the command arguments with two hyphens ( -- ). This syntax is useful when arguments might be mistaken for command line options.
' <schema> ... </schema> ' Search CQL only: Single quotation marks ( ' ) surround an entire XML schema declaration.
@xml_entity='xml_entity_type' Search CQL only: Identify the entity and literal value to overwrite the XML element in the schema and solrConfig files.

UPDATE writes one or more column values to a row in a 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.

Assign new values to the row's columns in the SET clause. UPDATE cannot update the values of a row's primary key fields. To update a counter in a counter table, specify the increment or decrement to the counter column.
Note: Unlike the INSERT command, the UPDATE command supports counters. Otherwise, the UPDATE and INSERT operations are identical.

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.

The IF EXISTS or IF keywords introduce a lightweight transaction:
UPDATE cycling.cyclist_name
SET comments = 'Rides hard, gets along with others, a real winner'
WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa
IF EXISTS;
Use the IF keyword to introduce a condition that must return TRUE for the update to succeed. Using an IF condition incurs a performance hit associated with using Paxos to support linearizable consistency.

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

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. Setting TTL on a column using the INSERT or UPDATE command overrides the table TTL.

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

Warning: The database storage engine can only encode TTL timestamps through January 19 2038 03:14:07 UTC due to the Year 2038 problem. The TTL date overflow policy determines whether requests with expiration timestamps later than the maximum date are rejected or inserted. See -Dcassandra.expiration_date_overflow_policy=POLICY.
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, the database 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 condition
Performs validation before updating records (lightweight transaction). Use as follows:
  • IF EXISTS - One or more rows must match the query. If no rows match, the statement fails.
    UPDATE cycling.cyclist_name
SET comment = 'Rides hard, gets along with others, a real winner'
WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa 
IF EXISTS;
    Tip: When no rows match an UPDATE statement that does not have IF EXISTS, a new record is created.
  • IF conditional_statement - Test non-primary key columns on rows that match the query. Applies the update to rows that return true. If no rows match the query and the conditional statement tests for NULL, a new record is inserted.
    UPDATE cycling.cyclist_name
SET comment = 'Rides hard, gets along with others, a real winner'
WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa 
IF comment = NULL;
Warning: Using IF statements impact performance.

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
  );
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.

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

Since the database processes an UPDATE as an upsert, it is possible to create a new row by updating it in a table. Example: to create a new partition in the cyclists table, whose primary key is (id), you can UPDATE the partition with id e7cd5752-bc0d-4157-a80f-7523add8dbcd, even though it does not exist yet:
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."

CAUTION: The Java List Index is not thread safe. The set or map collection types are safer for updates.

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 the database handles them differently in important ways.

For example: CQL provides a straightforward method for creating a new row containing a collection map:
UPDATE cycling.upcoming_calendar 
SET description = 
{'Criterium du Dauphine' : 'Easy race', 
 'Tour du Suisse' : 'Hard uphill race'} 
WHERE year = 2015 AND month = 6;
The easiest way to add a new entry to the map is to use the + operator as described above:
UPDATE cycling.upcoming_calendar 
SET description = description + { 'Tour de France' : 'Very competitive'} 
WHERE year = 2015 AND month = 6;
You may, however, try to add the new entry with a command that overwrites the first two and adds the new one:
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, the database 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 DataStax Distribution of Apache Cassandra™ (DDAC), 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

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:

Standard output if UPDATE ... IF EXISTS returns TRUE

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

Standard output if UPDATE ... IS EXISTS returns FALSE

Use IF condition to apply tests to one or more column values in the selected row:
UPDATE cyclist_id
SET id = 15a116fc-b833-4da6-ab9a-4a3775750239
WHERE lastname = 'WELTEN'
  AND firstname = 'Bram'
IF age = 18;
If all the conditions return TRUE, standard output is the same as if IF EXISTS returned true (see above). If any of the conditions fails, standard output displays False in the [applied] column and also displays information about the condition that failed:

Standard output if UPDATE ... IF returns FALSE

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 creates a new row if no matching row is found. New rows are not immediately available for lightweight transactions 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, 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.