---
title: "UPDATE"
url: "https://docs.yugabyte.com/stable/api/ysql/the-sql-language/statements/dml_update/"
---

# UPDATE

Use UPDATE to modify values of specified columns in all rows that meet certain conditions. When conditions are not provided in WHERE clause, all rows update.

## Synopsis

Use the `UPDATE` statement to modify the values of specified columns in all rows that meet certain conditions, and when conditions are not provided in WHERE clause, all rows are updated. `UPDATE` outputs the number of rows that are being updated.

## Syntax

- [![Grammar Icon](/icons/file-lines.svg)Grammar](#grammar-2)
- [![Diagram Icon](/icons/diagram.svg)Diagram](#diagram-2)

```output.ebnf
update ::= [ with_clause ]  UPDATE table_expr [ [ AS ] alias ]  SET 
           update_item [ , ... ] [ FROM from_item ]  
           [ WHERE boolean_expression | WHERE CURRENT OF cursor_name ] 
            [ returning_clause ]

returning_clause ::= RETURNING { * | { output_expression 
                                     [ [ AS ] output_name ] } 
                                     [ , ... ] }

update_item ::= column_name = column_value
                | ( column_names ) = [ ROW ] ( column_values )
                | ( column_names ) = subquery

column_values ::= { expression | DEFAULT } [ , ... ]

column_names ::= column_name [ , ... ]
```

#### update

[with\_clause](/stable/api/ysql/syntax_resources/grammar_diagrams#with-clause)UPDATE[table\_expr](/stable/api/ysql/syntax_resources/grammar_diagrams#table-expr)AS[alias](/stable/api/ysql/syntax_resources/grammar_diagrams#alias)SET,[update\_item](#update-item)FROM[from\_item](/stable/api/ysql/syntax_resources/grammar_diagrams#from-item)WHERE[boolean\_expression](/stable/api/ysql/syntax_resources/grammar_diagrams#boolean-expression)WHERECURRENTOF[cursor\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#cursor-name)[returning\_clause](#returning-clause)

#### returning\_clause

RETURNING\*,[output\_expression](/stable/api/ysql/syntax_resources/grammar_diagrams#output-expression)AS[output\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#output-name)

#### update\_item

[column\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#column-name)=[column\_value](/stable/api/ysql/syntax_resources/grammar_diagrams#column-value)([column\_names](#column-names))=ROW([column\_values](#column-values))([column\_names](#column-names))=[subquery](/stable/api/ysql/syntax_resources/grammar_diagrams#subquery)

#### column\_values

,[expression](/stable/api/ysql/syntax_resources/grammar_diagrams#expression)DEFAULT

#### column\_names

,[column\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#column-name)

Table inheritance

By default, `UPDATE` on a table also updates matching rows in tables that inherit from it. Use `ONLY` before the table name to update the named table alone; `*` after it makes the default explicit. See [Table inheritance](/stable/api/ysql/the-sql-language/ddl-inherit/ "Table inheritance").

See the section [The WITH clause and common table expressions](/stable/api/ysql/the-sql-language/with-clause/ "The WITH clause and common table expressions") for more information about the semantics of the `common_table_expression` grammar rule.

## Semantics

You can update columns that are part of an index key, including `PRIMARY KEY` columns. The updated values must still satisfy all applicable constraints, such as primary key and unique index constraints.

- While the `WHERE` clause allows a wide range of operators, the exact conditions used in the where clause have significant performance considerations (especially for large datasets). For the best performance, use a `WHERE` clause that provides values for all columns in `PRIMARY KEY` or `INDEX KEY`.

### *with\_query*

Specify the subqueries that are referenced by name in the `UPDATE` statement.

### *table\_name*

Specify the name of the table to be updated.

### *alias*

Specify the identifier of the target table within the `UPDATE` statement. When an alias is specified, it must be used in place of the actual table in the statement.

### *column\_name*

Specify the column in the table to be updated.

### *expression*

Specify the value to be assigned to a column. When the expression is referencing a column, the old value of this column is used to evaluate.

### *from\_item*

A table expression that enables referencing columns from additional tables within the `WHERE` clause. This follows the identical syntax conventions as the `FROM` clause in a `SELECT` statement, including the ability to define table aliases. The target table should not be included as a `from_item` unless performing a self-join operation, in which case the target table must appear with an alias in the `from_item` list.

### *output\_expression*

Specify the value to be returned. When the `output_expression` is referencing a column, the new value of this column (updated value) is used to evaluate.

### *subquery*

Specify the SELECT subquery statement. Its selected values will be assigned to the specified columns.

## Examples

Create a sample table, insert a few rows, then update the inserted rows.

```plpgsql
yugabyte=# CREATE TABLE sample(k1 int, k2 int, v1 int, v2 text, PRIMARY KEY (k1, k2));
```

```plpgsql
yugabyte=# INSERT INTO sample VALUES (1, 2.0, 3, 'a'), (2, 3.0, 4, 'b'), (3, 4.0, 5, 'c');
```

```plpgsql
yugabyte=# SELECT * FROM sample ORDER BY k1;
```

```
 k1 | k2 | v1 | v2
----+----+----+----
  1 |  2 |  3 | a
  2 |  3 |  4 | b
  3 |  4 |  5 | c
(3 rows)
```

```plpgsql
yugabyte=# UPDATE sample SET v1 = v1 + 3, v2 = '7' WHERE k1 = 2 AND k2 = 3;
```

```
UPDATE 1
```

```plpgsql
yugabyte=# SELECT * FROM sample ORDER BY k1;
```

```
 k1 | k2 | v1 | v2
----+----+----+----
  1 |  2 |  3 | a
  2 |  3 |  7 | 7
  3 |  4 |  5 | c
(3 rows)
```

## See also

- [DELETE](/stable/api/ysql/the-sql-language/statements/dml_delete/ "DELETE")
- [INSERT](/stable/api/ysql/the-sql-language/statements/dml_insert "INSERT")
- [SELECT](/stable/api/ysql/the-sql-language/statements/dml_select/ "SELECT")