--- title: "UPDATE" url: "https://docs.yugabyte.com/stable/api/ycql/dml_update/" --- # UPDATE Use the UPDATE statement to update one or more column values for a row in table. ## 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 UPDATE[table\_name](/stable/api/ycql/grammar_diagrams#table-name)USING[using\_expression](/stable/api/ycql/grammar_diagrams#using-expression)SET,[assignment](/stable/api/ycql/grammar_diagrams#assignment)WHERE[where\_expression](/stable/api/ycql/grammar_diagrams#where-expression)IF[if\_expression](/stable/api/ycql/grammar_diagrams#if-expression)NOTEXISTS[if\_expression](/stable/api/ycql/grammar_diagrams#if-expression)ORNOTEXISTSRETURNS STATUS AS ROW ### using\_expression ```ebnf using_expression = ttl_or_timestamp_expression { 'AND' ttl_or_timestamp_expression }; ``` AND[ttl\_or\_timestamp\_expression](/stable/api/ycql/grammar_diagrams#ttl-or-timestamp-expression) ### ttl\_or\_timestamp\_expression ```ebnf ttl_or_timestamp_expression = 'TTL' ttl_expression | 'TIMESTAMP' timestamp_expression; ``` TTL[ttl\_expression](/stable/api/ycql/grammar_diagrams#ttl-expression)TIMESTAMP[timestamp\_expression](/stable/api/ycql/grammar_diagrams#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](/stable/api/ycql#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](/stable/api/ycql/ddl_create_table#table-properties-1 "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](/stable/api/ycql/batch#row-status "batch docs"). ## See also - [CREATE TABLE](/stable/api/ycql/ddl_create_table "CREATE TABLE") - [DELETE](/stable/api/ycql/dml_delete/ "DELETE") - [INSERT](/stable/api/ycql/dml_insert "INSERT") - [SELECT](/stable/api/ycql/dml_select/ "SELECT") - [Expression](/stable/api/ycql#expressions "Expression")