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 condition [ AND condition ] ) ] ;
INSERT
or UPDATE
command if access permissions are enabled, a user must
be granted MODIFY
or ALL PERMISSIONS
on the base
table.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.
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.Warning: The database storage engine can only encode TTL timestamps throughJanuary 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 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 withAND 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. - To specify one row, use
- 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 haveIF 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;
-
Examples
Updating a column
UPDATE cycling.cyclist_name SET firstname = NULL WHERE id IN ( 5b6962dd-3f90-4c93-8f61-eabfa4a803e2, fb372533-eb95-4bb4-8685-6ef61e994caa );
UPDATE cycling.cyclist_name SET firstname = 'Marianne', lastname = 'VOS' WHERE id = 88b8fd18-b1ed-4e96-bf79-4280797cba80;
Updating a counter column
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
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
UPDATE cycling.upcoming_calendar SET events = ['Criterium du Dauphine','Tour de Suisse'] WHERE year=2015 AND month=06;
UPDATE cycling.upcoming_calendar SET events = ['Tour de France'] + events WHERE year=2015 AND month=06;
UPDATE cycling.upcoming_calendar SET events = events + ['Tour de France'] WHERE year=2017 AND month=05;
UPDATE cycling.upcoming_calendar SET events[2] = 'Tour de France' WHERE year=2015 AND month=06;
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."
set
or
map
collection types are safer for updates.Updating a set
UPDATE cycling.cyclist_career_teams SET teams = teams + {'Team DSB - Ballast Nedam'} WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;
UPDATE cycling.cyclist_career_teams SET teams = teams - {'DSB Bank Nederland bloeit'} WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;
UPDATE cycling.cyclist_career_teams SET teams = {} WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;
Updating a map
UPDATE cycling.upcoming_calendar SET description = description + {'Criterium du Dauphine' : 'Easy race'} WHERE year = 2015 AND month = 06 ;
events
:UPDATE cycling.upcoming_calendar SET events[2] = 'Vuelta Ciclista a Venezuela' WHERE year = 2015 AND month = 06;
UPDATE cycling.upcoming_calendar USING TTL 10000000 SET events[2] = 'Vuelta Ciclista a Venezuela' WHERE year = 2015 AND month = 06;
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.
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
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.
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.
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:
[applied] ----------- True
- If a record matches and the condition returns false, the query fails and following
shows an example of the
output:
[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.
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.
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.)