The UPDATE statement updates rows in a table.
{{site.data.alerts.callout_danger}} If you update a row that contains a column referenced by a foreign key constraint and has an ON UPDATE action, all of the dependent rows will also be updated. {{site.data.alerts.end}}
Required privileges
The user must have the SELECT and UPDATE privileges on the table.
Synopsis
{% include {{ page.version.version }}/sql/diagrams/update.html %}
Parameters
| Parameter | Description |
|---|---|
common_table_expr |
See Common Table Expressions. |
table_name |
The name of the table that contains the rows you want to update. |
AS table_alias_name |
An alias for the table name. When an alias is provided, it completely hides the actual table name. |
column_name |
The name of the column whose values you want to update. |
a_expr |
The new value you want to use, the aggregate function you want to perform, or the scalar expression you want to use. |
DEFAULT |
To fill columns with their default values, use DEFAULT VALUES in place of a_expr. To fill a specific column with its default value, leave the value out of the a_expr or use DEFAULT at the appropriate position. |
column_name |
The name of a column to update. |
select_stmt |
A selection query. Each value must match the data type of its column on the left side of =. |
WHERE a_expr |
a_expr must be a scalar expression that returns Boolean values using columns (e.g., <column> = <value>). Update rows that return TRUE.Without a WHERE clause in your statement, UPDATE updates all rows in the table. |
sort_clause |
An ORDER BY clause. See Ordering Query Results for more details. |
limit_clause |
A LIMIT clause. See Limiting Query Results for more details. |
RETURNING target_list |
Return values based on rows updated, where target_list can be specific column names from the table, * for all columns, or computations using scalar expressions. To return nothing in the response, not even the number of rows updated, use RETURNING NOTHING. |
Examples
Update a single column in a single row
> SELECT * FROM accounts;
+----+----------+----------+
| id | balance | customer |
+----+----------+----------+
| 1 | 10000.50 | Ilya |
| 2 | 4000.0 | Julian |
| 3 | 8700.0 | Dario |
| 4 | 3400.0 | Nitin |
+----+----------+----------+
(4 rows)
> UPDATE accounts SET balance = 5000.0 WHERE id = 2;
> SELECT * FROM accounts;
+----+----------+----------+
| id | balance | customer |
+----+----------+----------+
| 1 | 10000.50 | Ilya |
| 2 | 5000.0 | Julian |
| 3 | 8700.0 | Dario |
| 4 | 3400.0 | Nitin |
+----+----------+----------+
(4 rows)
Update multiple columns in a single row
> UPDATE accounts SET (balance, customer) = (9000.0, 'Kelly') WHERE id = 2;
> SELECT * FROM accounts;
+----+----------+----------+
| id | balance | customer |
+----+----------+----------+
| 1 | 10000.50 | Ilya |
| 2 | 9000.0 | Kelly |
| 3 | 8700.0 | Dario |
| 4 | 3400.0 | Nitin |
+----+----------+----------+
(4 rows)
> UPDATE accounts SET balance = 6300.0, customer = 'Stanley' WHERE id = 3;
> SELECT * FROM accounts;
+----+----------+----------+
| id | balance | customer |
+----+----------+----------+
| 1 | 10000.50 | Ilya |
| 2 | 9000.0 | Kelly |
| 3 | 6300.0 | Stanley |
| 4 | 3400.0 | Nitin |
+----+----------+----------+
(4 rows)
Update using SELECT statement
> UPDATE accounts SET (balance, customer) =
(SELECT balance, customer FROM accounts WHERE id = 2)
WHERE id = 4;
> SELECT * FROM accounts;
+----+----------+----------+
| id | balance | customer |
+----+----------+----------+
| 1 | 10000.50 | Ilya |
| 2 | 9000.0 | Kelly |
| 3 | 6300.0 | Stanley |
| 4 | 9000.0 | Kelly |
+----+----------+----------+
(4 rows)
Update with default values
> UPDATE accounts SET balance = DEFAULT where customer = 'Stanley';
> SELECT * FROM accounts;
+----+----------+----------+
| id | balance | customer |
+----+----------+----------+
| 1 | 10000.50 | Ilya |
| 2 | 9000.0 | Kelly |
| 3 | NULL | Stanley |
| 4 | 9000.0 | Kelly |
+----+----------+----------+
(4 rows)
Update all rows
{{site.data.alerts.callout_danger}} If you do not use the WHERE clause to specify the rows to be updated, the values for all rows will be updated. {{site.data.alerts.end}}
> UPDATE accounts SET balance = 5000.0;
> SELECT * FROM accounts;
+----+---------+----------+
| id | balance | customer |
+----+---------+----------+
| 1 | 5000.0 | Ilya |
| 2 | 5000.0 | Kelly |
| 3 | 5000.0 | Stanley |
| 4 | 5000.0 | Kelly |
+----+---------+----------+
(4 rows)
Update and return values
In this example, the RETURNING clause returns the id value of the row updated. The language-specific versions assume that you have installed the relevant client drivers.
{{site.data.alerts.callout_success}}This use of RETURNING mirrors the behavior of MySQL's last_insert_id() function.{{site.data.alerts.end}}
{{site.data.alerts.callout_info}}When a driver provides a query() method for statements that return results and an exec() method for statements that do not (e.g., Go), it's likely necessary to use the query() method for UPDATE statements with RETURNING.{{site.data.alerts.end}}