UPDATE statement [YCQL]

Synopsis

Use the UPDATE statement to update one or more column values for a row in table.

YugabyteDB can only update one row at a time. Updating multiple rows is currently not supported.

Syntax

Diagram

using_expression

ebnf

using_expression = ttl_or_timestamp_expression { 'AND' ttl_or_timestamp_expression };

ttl_or_timestamp_expression

ebnf

ttl_or_timestamp_expression = 'TTL' ttl_expression | 'TIMESTAMP' timestamp_expression;

ebnf

update ::= UPDATE table_name [ USING using_expression ] SET assignment
           [ , ... ]  WHERE where_expression
           [ IF { if_expression
                  | [ NOT ] EXISTS
                  | if_expression OR [ NOT ] EXISTS } ]
           [ RETURNS STATUS AS ROW ]


assignment ::= { column_name | column_name'['index_expression']' } '=' expression

Where

table_name is an identifier (possibly qualified with a keyspace name).
Restrictions for ttl_expression, where_expression, and if_expression are covered in the Semantics section.
See Expressions for more information on syntax rules.

Semantics

An error is raised if the specified table_name does not exist.
Update statement uses upsert semantics, meaning it inserts the row being updated if it does not already exists.
The USING TIMESTAMP clause indicates you would like to perform the UPDATE as if it was done at the timestamp provided by the user. The timestamp is the number of microseconds since epoch.
Note: You should either use the USING TIMESTAMP clause in all of your statements or none of them. Using a mix of statements where some have USING TIMESTAMP and others do not will lead to very confusing results.
Updating rows USING TTL is not supported on tables with transactions enabled.
You cannot update the columns in the primary key. As a workaround, you have to delete the row and insert a new row.
UPDATE is always done at QUORUM consistency level irrespective of setting.

`WHERE` clause

The where_expression and if_expression must evaluate to boolean values.
The where_expression must specify conditions for all primary-key columns.
The where_expression must not specify conditions for any regular columns.
The where_expression can only apply AND and = operators. Other operators are not yet supported.

`IF` clause

The if_expression can only apply to non-key columns (regular columns).
The if_expression can contain any logical and boolean operators.

`USING` clause

ttl_expression must be an integer value (or a bind variable marker for prepared statements).
timestamp_expression must be an integer value (or a bind variable marker for prepared statements).

Examples

Update a value in a table

sql

ycqlsh:example> CREATE TABLE employees(department_id INT,
                                      employee_id INT,
                                      name TEXT,
                                      age INT,
                                      PRIMARY KEY(department_id, employee_id));

sql

ycqlsh:example> INSERT INTO employees(department_id, employee_id, name, age) VALUES (1, 1, 'John', 30);

Update the value of a non primary-key column.

sql

ycqlsh:example> UPDATE employees SET name = 'Jack' WHERE department_id = 1 AND employee_id = 1;

Using upsert semantics to update a non-existent row (that is, insert the row).

sql

ycqlsh:example> UPDATE employees SET name = 'Jane', age = 40 WHERE department_id = 1 AND employee_id = 2;

sql

ycqlsh:example> SELECT * FROM employees;

output

 department_id | employee_id | name | age
---------------+-------------+------+-----
             1 |           1 | Jack |  30
             1 |           2 | Jane |  40

Conditional update using the `IF` clause

The supported expressions are allowed in the 'SET' assignment targets.

sql

ycqlsh:example> UPDATE employees SET age = age + 1 WHERE department_id = 1 AND employee_id = 1 IF name = 'Jack';

output

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

Using upsert semantics to add a row, age is not set so will be 'null'.

sql

ycqlsh:example> UPDATE employees SET name = 'Joe' WHERE department_id = 2 AND employee_id = 1 IF NOT EXISTS;

output

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

sql

ycqlsh:example> SELECT * FROM employees;

output

 department_id | employee_id | name | age
---------------+-------------+------+------
             2 |           1 |  Joe | null
             1 |           1 | Jack |   31
             1 |           2 | Jane |   40

Update with expiration time using the `USING TTL` clause

The updated values will persist for the TTL duration.

sql

ycqlsh:example> UPDATE employees USING TTL 10 SET age = 32 WHERE department_id = 1 AND employee_id = 1;

sql

ycqlsh:example> SELECT * FROM employees WHERE department_id = 1 AND employee_id = 1;

output

 department_id | employee_id | name | age
---------------+-------------+------+------
             1 |           1 | Jack |   32

11 seconds after the update (value will have expired).

sql

ycqlsh:example> SELECT * FROM employees WHERE department_id = 1 AND employee_id = 1;

output

 department_id | employee_id | name | age
---------------+-------------+------+------
             1 |           1 | Jack | null

Update row with the `USING TIMESTAMP` clause

You can do this as follows:

sql

ycqlsh:foo> INSERT INTO employees(department_id, employee_id, name, age) VALUES (1, 4, 'Jeff', 20) USING TIMESTAMP 1000;

sql

ycqlsh:foo> SELECT * FROM employees;

output

 department_id | employee_id | name | age
---------------+-------------+------+------
             1 |           1 | Jack | null
             1 |           2 | Jane |   40
             1 |           4 | Jeff |   20
             2 |           1 |  Joe | null

(4 rows)

Now update the employees table.

sql

ycqlsh:foo> UPDATE employees USING TIMESTAMP 500 SET age = 30 WHERE department_id = 1 AND employee_id = 4;

Not applied since timestamp is lower than 1000.

sql

ycqlsh:foo> SELECT * FROM employees;

output

 department_id | employee_id | name | age
---------------+-------------+------+------
             1 |           1 | Jack | null
             1 |           2 | Jane |   40
             1 |           4 | Jeff |   20
             2 |           1 |  Joe | null

(4 rows)

sql

ycqlsh:foo> UPDATE employees USING TIMESTAMP 1500 SET age = 30 WHERE department_id = 1 AND employee_id = 4;

Applied since timestamp is higher than 1000.

sql

ycqlsh:foo> SELECT * FROM employees;

output

 department_id | employee_id | name | age
---------------+-------------+------+------
             1 |           1 | Jack | null
             1 |           2 | Jane |   40
             1 |           4 | Jeff |   30
             2 |           1 |  Joe | null

(4 rows)

RETURNS STATUS AS ROW

When executing a batch in YCQL, the protocol returns only one error or return status. The RETURNS STATUS AS ROW feature addresses this limitation and adds a status row for each statement.

See examples in batch docs.

Synopsis

Syntax

Diagram

using_expression

ttl_or_timestamp_expression

Semantics

WHERE clause

IF clause

USING clause

Examples

Update a value in a table

Conditional update using the IF clause

Update with expiration time using the USING TTL clause

Update row with the USING TIMESTAMP clause

RETURNS STATUS AS ROW

See also

`WHERE` clause

`IF` clause

`USING` clause

Conditional update using the `IF` clause

Update with expiration time using the `USING TTL` clause

Update row with the `USING TIMESTAMP` clause