UPDATE
Synopsis
Use the UPDATE statement to update one or more column values for a row in table.
Note
YugabyteDB can only update one row at a time. Updating multiple rows is currently not supported.Syntax
Diagram
using_expression
using_expression = ttl_or_timestamp_expression { 'AND' ttl_or_timestamp_expression };
ttl_or_timestamp_expression
ttl_or_timestamp_expression = 'TTL' ttl_expression | 'TIMESTAMP' timestamp_expression;
update ::= UPDATE table_name [ USING using_expression ] SET assignment
[ , ... ] WHERE where_expression
[ IF { [ NOT ] EXISTS | if_expression } ]
[ RETURNS STATUS AS ROW ]
assignment ::= { column_name | column_name'['index_expression']' } '=' expression
Where
table_nameis an identifier (possibly qualified with a keyspace name).- Restrictions for
ttl_expression,where_expression, andif_expressionare covered in the Semantics section below. - See Expressions for more information on syntax rules.
Semantics
- An error is raised if the specified
table_namedoes not exist. - Update statement uses upsert semantics, meaning it inserts the row being updated if it does not already exists.
- The
USING TIMESTAMPclause 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 TIMESTAMPclause in all of your statements or none of them. Using a mix of statements where some haveUSING TIMESTAMPand others do not will lead to very confusing results. - Updating rows
USING TTLis not supported on tables with transactions enabled.
WHERE clause
- The
where_expressionandif_expressionmust evaluate to boolean values. - The
where_expressionmust specify conditions for all primary-key columns. - The
where_expressionmust not specify conditions for any regular columns. - The
where_expressioncan only applyANDand=operators. Other operators are not yet supported.
IF clause
- The
if_expressioncan only apply to non-key columns (regular columns). - The
if_expressioncan contain any logical and boolean operators.
USING clause
ttl_expressionmust be an integer value (or a bind variable marker for prepared statements).timestamp_expressionmust be an integer value (or a bind variable marker for prepared statements).
Examples
Update a value in a table
ycqlsh:example> CREATE TABLE employees(department_id INT,
employee_id INT,
name TEXT,
age INT,
PRIMARY KEY(department_id, employee_id));
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.
ycqlsh:example> UPDATE employees SET name = 'Jack' WHERE department_id = 1 AND employee_id = 1;
Using upsert semantics to update a non-existent row (i.e. insert the row).
ycqlsh:example> UPDATE employees SET name = 'Jane', age = 40 WHERE department_id = 1 AND employee_id = 2;
ycqlsh:example> SELECT * FROM employees;
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.
ycqlsh:example> UPDATE employees SET age = age + 1 WHERE department_id = 1 AND employee_id = 1 IF name = 'Jack';
[applied]
-----------
True
Using upsert semantics to add a row, age is not set so will be 'null'.
ycqlsh:example> UPDATE employees SET name = 'Joe' WHERE department_id = 2 AND employee_id = 1 IF NOT EXISTS;
[applied]
-----------
True
ycqlsh:example> SELECT * FROM employees;
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.
ycqlsh:example> UPDATE employees USING TTL 10 SET age = 32 WHERE department_id = 1 AND employee_id = 1;
ycqlsh:example> SELECT * FROM employees WHERE department_id = 1 AND employee_id = 1;
department_id | employee_id | name | age
---------------+-------------+------+------
1 | 1 | Jack | 32
11 seconds after the update (value will have expired).
ycqlsh:example> SELECT * FROM employees WHERE department_id = 1 AND employee_id = 1;
department_id | employee_id | name | age
---------------+-------------+------+------
1 | 1 | Jack | null
Update row with the USING TIMESTAMP clause
You can do this as shown below.
ycqlsh:foo> INSERT INTO employees(department_id, employee_id, name, age) VALUES (1, 4, 'Jeff', 20) USING TIMESTAMP 1000;
ycqlsh:foo> SELECT * FROM employees;
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.
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.
ycqlsh:foo> SELECT * FROM employees;
department_id | employee_id | name | age
---------------+-------------+------+------
1 | 1 | Jack | null
1 | 2 | Jane | 40
1 | 4 | Jeff | 20
2 | 1 | Joe | null
(4 rows)
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.
ycqlsh:foo> SELECT * FROM employees;
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.