Update

Purpose

An Update statement changes the value of columns in the selected rows of a table.

In Cassandra, Updates are in effect Upserts. When an Update is performed on a row that does not exist, the row will be inserted.

Syntax

UPDATE table_name SET column_name = expression

[, column_name = expression] [WHERE conditions]

table_name

is the name of the table for which you want to update values.

column_name

is the name of a column, the value of which is to be changed. Multiple column values can be changed in a single statement.

expression

is the new value for the column. The expression can be a constant value or a subquery that returns a single value. Subqueries must be enclosed in parentheses.

Example A

The following example changes every record that meets the conditions in the Where clause. In this case, the salary and exempt status are changed for all employees having the employee ID E10001. Because employee IDs are unique in the emp table, only one record is updated.

UPDATE emp SET salary=32000, exempt=1

WHERE emp_id = 'E10001'

Example B

The following example uses a subquery. In this example, the salary is changed to the average salary in the company for the employee having employee ID E10001.

UPDATE emp SET salary = (SELECT avg(salary) FROM emp)

WHERE emp_id = 'E10001'

Notes

Update is supported for primitive types, non-nested Tuple types, and non-nested user-defined types. Update is also supported for values in non-nested Map types. The driver does not support updates on List types, Set types, or keys in Map types because the values in each are part of the primary key of their respective child tables and primary key columns cannot be updated. If an Update is attempted when not allowed, the driver issues the following error message:

[DataDirect][Cassandra JDBC Driver][Cassandra]syntax error or access rule violation: UPDATE not permitted for column: column_name

See "Complex Type Normalization" for details.

Update is supported for Counter columns when all the other columns in the row comprise that row’s primary key. The Counter column itself is the only updatable field in the row. When updating a Counter column on an existing row, the Counter column is updated according to the increment (or decrement) specified in the SQL statement. When updating a Counter column for which there is no existing row, the values of the columns that comprise the row’s primary key are inserted into the table alongside the value of the Counter column.

For example, consider the following table.

CREATE TABLE page_view_counts (
counter_value counter,
url_name varchar,
page_name varchar,
PRIMARYKEY (url_name, page_name));

The following Update can be performed on the page_view_counts table.

UPDATE page_view_counts
SET counter_value=counter_value + 1
WHERE url_name = 'www.progress.com' AND page_name = 'home'

This Update would provide the following output.

Note: Cassandra initially assigns a value of 0 (zero) to Counter columns. An increment or decrement can be specified in the SQL statement.

url_name | page_name | counter_value
------------------+-----------+---------------
www.progress.com | home | 1

A Where clause can be used to restrict which rows are updated.

To enable Insert, Update, and Delete, set the ReadOnly connection property to false.