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

UPDATE - 图1

using_expression

  1. using_expression = ttl_or_timestamp_expression { 'AND' ttl_or_timestamp_expression };

UPDATE - 图2

ttl_or_timestamp_expression

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

UPDATE - 图3

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

  2. Where
  4. - `table_name` is an identifier (possibly qualified with a keyspace name).
  5. - Restrictions for `ttl_expression`, `where_expression`, and `if_expression` are covered in the Semantics section below.
  6. - See [Expressions](..#expressions) for more information on syntax rules.
  8. ## Semantics
  9.  - An error is raised if the specified `table_name` does not exist.
  10.  - Update statement uses _upsert semantics_, meaning it inserts the row being updated if it does not already exists.
  11.  - The `USING TIMESTAMP` clause indicates we would like to perform the UPDATE as if it was done at the
  12.    timestamp provided by the user. The timestamp is the number of microseconds since epoch.
  13.  - **NOTE**: You should either use the `USING TIMESTAMP` clause in all of your statements or none of
  14.    them. Using a mix of statements where some have `USING TIMESTAMP` and others do not will lead to
  15.    very confusing results.
  17. ### `WHERE` Clause
  19.  - The `where_expression` and `if_expression` must evaluate to boolean values.
  20.  - The `where_expression` must specify conditions for all primary-key columns.
  21.  - The `where_expression` must not specifiy conditions for any regular columns.
  22.  - The `where_expression` can only apply `AND` and `=` operators. Other operators are not yet supported.
  24. ### `IF` Clause
  26.  - The `if_expression` can only apply to non-key columns (regular columns).
  27.  - The `if_expression` can contain any logical and boolean operators.
  29. ### `USING` Clause
  30.  - `ttl_expression` must be an integer value (or a bind variable marker for prepared statements).
  31.  - `timestamp_expression` must be an integer value (or a bind variable marker for prepared statements).
  33. ## Examples
  35. ### Update a value in a table
  37. 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);

  2. Update the value of a non primary-key column.

sqlcqlsh:example> UPDATE employees SET name = ‘Jack’ WHERE department_id = 1 AND employee_id = 1;

  2. 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

  2. ### Conditional update using the `IF` clause
  5. 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]

  1.   True
  1. 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]

  1.   True

sqlcqlsh:example> SELECT * FROM employees;

department_id | employee_id | name | age———————-+——————-+———+———2 | 1 | Joe | null1 | 1 | Jack | 311 | 2 | Jane | 40

  2. ### Update with expiration time using the `USING TTL` clause.
  4. 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

  1. 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

  2. ### Update row with the `USING TIMESTAMP` clause.
  4. 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)

  2. 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)

  2. 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 TABLEDELETEINSERTSELECTExpressionOther CQL Statements