UPDATE

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 <condition> [ AND <condition> ] ] ;

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.

Syntax legend

Legend
Syntax conventions	Description
UPPERCASE	Literal keyword.
Lowercase	Not literal.
`< >`	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.
`<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.

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

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.

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

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.

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

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.

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;

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

Using IF statements impact performance, see linearizable consistency.

For examples, see Conditionally updating columns.

Examples

Updating a column

Update a column in several rows at once:

UPDATE cycling.cyclist_name SET firstname = NULL
WHERE id IN (
  5b6962dd-3f90-4c93-8f61-eabfa4a803e2,
  fb372533-eb95-4bb4-8685-6ef61e994caa
);

Update multiple columns in a single row:

UPDATE cycling.cyclist_name
SET
  firstname = 'Marianne',
  lastname = 'VOS'
WHERE id = 88b8fd18-b1ed-4e96-bf79-4280797cba80;

Updating using a timestamp and TTL

To update a set (or any collection) value with a timestamp and TTL, specify the value:

UPDATE cycling.cyclist_sponsors_expire 
  USING TIMESTAMP 200 AND TTL 20000 
  SET sponsorship += { 'Tag Heuer' } 
  WHERE cyclist_name = 'PRIETO, Marcela';

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.cyclist_name
SET
  firstname = 'Anna',
  lastname = 'VAN DER BREGGEN' 
WHERE id = e7cd5752-bc0d-4157-a80f-7523add8dbcd;

Updating a list

To insert values into the list:

INSERT INTO cycling.upcoming_calendar ( year, month, events ) 
VALUES ( 2015, 06, [ 'Criterium du Dauphine', 'Tour de Suisse' ] );

INSERT INTO cycling.upcoming_calendar ( year, month, events ) 
VALUES ( 2015, 07, [ 'Tour de France' ] );

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[2] = 'Tour de France' 
  WHERE year = 2015 AND month = 06;

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 - [ 'Tour de France' ] 
  WHERE year = 2015 AND month = 06;

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

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;

DELETE teams FROM cycling.cyclist_career_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 AND month = 06;

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 = 2015 AND month = 06;

To set the a TTL for each map element:

UPDATE cycling.upcoming_calendar USING TTL 10000000
  SET events[2] = 'Vuelta Ciclista a Venezuela' 
  WHERE year = 2015 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.

You may, however, try to add the new entry with a command that overwrites the first two and adds the new one.

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

To change the value of an individual field value in a user-defined type with non-collection fields, use the UPDATE command:

UPDATE cycling.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 only apply the update if the query matches a row:

UPDATE cycling.cyclist_id SET id = UUID() 
WHERE lastname = 'WELTEN'
  AND firstname = 'Bram'
  AND age = 18
IF EXISTS;

If the row exists (returns true), the following is output:
```
 [applied]
-----------
      True
```
If no row exists (returns false), the command fails and the following is output:
```
 [applied]
-----------
     False
```

Use IF condition to apply tests to one or more other (non-primary key) column values in the matching row.

For example, to set a new UUID only if the id matches.

UPDATE cycling.cyclist_id SET id = UUID() 
WHERE lastname = 'WELTEN'
  AND firstname = 'Bram'
  AND age = 18
IF id = 18f471bf-f631-4bc4-a9a2-d6f6cf5ea503;

If a record matches and the condition returns TRUE, the update is applied and following is output:

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

 [applied]
-----------
      True

If a record matches and the condition returns false, the query fails and following shows an example of the output:

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

 [applied] | id
-----------+--------------------------------------
     False | 863e7103-c03b-48c3-a11c-42376aa77291

If no record matches and the condition is testing for a non-null value such as id = 18f471bf-f631-4bc4-a9a2-d6f6cf5ea503 the query also fails.

When the IF condition tests for a null value, for example:

# UPDATE cycling.cyclist_id SET id = UUID() 
# WHERE lastname = 'Smith'
#   AND firstname = 'Joe'
#   AND age = 22
# IF id = NULL;

A record matches and the id column has no value, a value is inserted.
A record matches and the id column has a value (is not null), the statement fails.
No record matches, then a new record is created.

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:

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

BEGIN BATCH
     INSERT INTO cycling.mytable (a, b, d) values (7, 7, 'a');
     UPDATE cycling.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.)

UPDATE

Synopsis

Examples

Updating a column

Update multiple columns in a single row:

Updating using a timestamp and TTL

Updating a counter column

Creating a partition using UPDATE

Updating a list

Updating a set

Updating a map

About updating sets and maps caution

Updating a UDT with non-collection fields

Conditionally updating columns

Performing conditional updates in a BATCH

Was this helpful?

Give Feedback