## Synopsis Use the `UPDATE` statement to update one or more column values for a row in table. {{< note title="Note" >}} YugabyteDB can only update one row at a time. Updating multiple rows is currently not supported. {{< /note >}} ## Syntax ### Diagram UPDATEtable_nameUSINGusing_expressionSET,assignmentWHEREwhere_expressionIFif_expressionNOTEXISTSif_expressionORNOTEXISTSRETURNS STATUS AS ROW ### using_expression ```ebnf using_expression = ttl_or_timestamp_expression { 'AND' ttl_or_timestamp_expression }; ``` ANDttl_or_timestamp_expression ### ttl_or_timestamp_expression ```ebnf ttl_or_timestamp_expression = 'TTL' ttl_expression | 'TIMESTAMP' timestamp_expression; ``` TTLttl_expressionTIMESTAMPtimestamp_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](..#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](./../ddl_create_table#table-properties-1). - 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](../batch#row-status). ## See also - [`CREATE TABLE`](../ddl_create_table) - [`DELETE`](../dml_delete/) - [`INSERT`](../dml_insert) - [`SELECT`](../dml_select/) - [`Expression`](..#expressions)