UPDATE
AttentionThis page documents an earlier version. Go to the latest (v2.1)version.
Synopsis
The UPDATE
statement updates one or more column values for a row in table. Currently, Yugabyte can only update one row at a time, updating multiple rows is not yet 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 [, assignment …]WHERE where_expression[ IF { [ NOT ] EXISTS | if_expression } ]
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 below.
- See [Expressions](..#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 we 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.
### `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 specifiy 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
You can do this as shown below.
sqlcqlsh:example> CREATE TABLE employees(department_id INT,employee_id INT,name TEXT,age INT,PRIMARY KEY(department_id, employee_id));
sqlcqlsh:example> INSERT INTO employees(department_id, employee_id, name, age) VALUES (1, 1, ‘John’, 30);
Update the value of a non primary-key column.
sqlcqlsh: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).
sqlcqlsh:example> UPDATE employees SET name = ‘Jane’, age = 40 WHERE department_id = 1 AND employee_id = 2;
sqlcqlsh:example> SELECT * FROM employees;
department_id | employee_id | name | age———————-+——————-+———+——-1 | 1 | Jack | 301 | 2 | Jane | 40
### Conditional update using the `IF` clause
The supported expressions are allowed in the 'SET' assignment targets.
sqlcqlsh: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'.
sqlcqlsh:example> UPDATE employees SET name = ‘Joe’ WHERE department_id = 2 AND employee_id = 1 IF NOT EXISTS;
[applied]
True
sqlcqlsh:example> SELECT * FROM employees;
department_id | employee_id | name | age———————-+——————-+———+———2 | 1 | Joe | null1 | 1 | Jack | 311 | 2 | Jane | 40
### Update with expiration time using the `USING TTL` clause.
The updated value(s) will persist for the TTL duration.
sqlcqlsh:example> UPDATE employees USING TTL 10 SET age = 32 WHERE department_id = 1 AND employee_id = 1;
sqlcqlsh: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).
sqlcqlsh: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.
sqlcqlsh:foo> INSERT INTO employees(department_id, employee_id, name, age) VALUES (1, 4, ‘Jeff’, 20) USING TIMESTAMP 1000;cqlsh:foo> SELECT * FROM employees;
department_id | employee_id | name | age———————-+——————-+———+———1 | 1 | Jack | null1 | 2 | Jane | 401 | 4 | Jeff | 202 | 1 | Joe | null
(4 rows)
Not applied since timestamp is lower than 1000.
sqlcqlsh:foo> UPDATE employees USING TIMESTAMP 500 SET age = 30 WHERE department_id = 1 AND employee_id = 4;cqlsh:foo> SELECT * FROM employees;
department_id | employee_id | name | age———————-+——————-+———+———1 | 1 | Jack | null1 | 2 | Jane | 401 | 4 | Jeff | 202 | 1 | Joe | null
(4 rows)
Applied since timestamp is higher than 1000.
sqlcqlsh:foo> UPDATE employees USING TIMESTAMP 1500 SET age = 30 WHERE department_id = 1 AND employee_id = 4;
cqlsh:foo> SELECT * FROM employees;
department_id | employee_id | name | age———————-+——————-+———+———1 | 1 | Jack | null1 | 2 | Jane | 401 | 4 | Jeff | 302 | 1 | Joe | null
(4 rows)
```
See Also
CREATE TABLE
DELETE
INSERT
SELECT
Expression
Other CQL Statements