UPDATE
Updates rows of a table.
Synopsis
[ WITH [ RECURSIVE ] <with_query> [, ...] ]
UPDATE [ ONLY ] <table_name> [ [ AS ] <alias> ]
SET { <column_name> = { <expression> | DEFAULT } |
( <column_name> [, ...] ) = [ ROW ] ( { <expression> | DEFAULT } [, ...] ) |
( <column_name> [, ...] ) = ( <sub-SELECT> )
} [, ...]
[ FROM <from_item> [, ...] ]
[ WHERE <condition> | WHERE CURRENT OF <cursor_name> ]
[ RETURNING * | <output_expression> [ [AS] <output_name> ] [, ...] ]
Description
UPDATE
changes the values of the specified columns in all rows that satisfy the condition. Only the columns to be modified need be mentioned in the SET
clause; columns not explicitly modified retain their previous values.
There are two ways to modify a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the FROM
clause. Which technique is more appropriate depends on the specific circumstances.
The optional RETURNING
clause causes UPDATE
to compute and return value(s) based on each row actually updated. Apache Cloudberry can compute any expression using the table's columns, and/or columns of other tables mentioned in FROM
. The new (post-update) values of the table's columns are used. The syntax of the RETURNING
list is identical to that of the output list of SELECT
.
You must have the UPDATE
privilege on the table, or at least on the column(s) that are listed to be updated. You must also have the SELECT
privilege on any column whose values are read in the expressions or condition.
Note As the default, Apache Cloudberry acquires an
EXCLUSIVE
lock on tables forUPDATE
operations on heap tables. When the Global Deadlock Detector is enabled, the lock mode forUPDATE
operations on heap tables isROW EXCLUSIVE
.
Parameters
with_query
The WITH
clause allows you to specify one or more subqueries that can be referenced by name in the UPDATE
query. See SELECT for details.
For an UPDATE
command that includes a WITH
clause, the clause can only contain SELECT
commands, the WITH
clause cannot contain a data-modifying command (INSERT
, UPDATE
, or DELETE
).
It is possible for the query (SELECT
statement) to also contain a WITH
clause. In such a case both sets of with_query can be referenced within the UPDATE
query, but the second one takes precedence since it is more closely nested.
table_name
The name (optionally schema-qualified) of the table to update. If ONLY
is specified before the table name, matching rows are updated in the named table only. If ONLY
is not specified, matching rows are also updated in any tables inheriting from the named table. Optionally, you can specify *
after the table name to explicitly indicate that descendant tables are included.
alias
A substitute name for the target table. When an alias is provided, it completely hides the actual name of the table. For example, given UPDATE foo AS f
, the remainder of the UPDATE
statement must refer to this table as f
not foo
.
column_name
The name of a column in the table named by table_name. The column name can be qualified with a subfield name or array subscript, if needed. Do not include the table's name in the specification of a target column; for example, UPDATE table_name SET table_name.col = 1
is invalid.
expression
An expression to assign to the column. The expression may use the old values of this and other columns in the table.
DEFAULT
Set the column to its default value (which will be NULL if no specific default expression has been assigned to it).
sub-SELECT
A SELECT
sub-query that produces as many output columns as are listed in the parenthesized column list preceding it. The sub-query must yield no more than one row when executed. If it yields one row, its column values are assigned to the target columns; if it yields no rows, NULL values are assigned to the target columns. The sub-query can refer to old values of the current row of the table being updated.
from_item
A table expression allowing columns from other tables to appear in the WHERE
condition and the update expressions. This uses the same syntax as the FROM
clause of a SELECT statement; for example, you can specify an alias for the table name. Do not repeat the target table as a from_item unless you intend a self-join (in which case it must appear with an alias in the from_item).
condition
An expression that returns a value of type boolean
. Only rows for which this expression returns true
will be updated.
cursor_name
The name of the cursor to use in a WHERE CURRENT OF
condition. The row to be updated is the one most recently fetched from the cursor. The cursor must be a non-grouping query on the UPDATE
's target table. Note that WHERE CURRENT OF
cannot be specified together with a Boolean condition. See DECLARE for more information about using cursors with WHERE CURRENT OF
.
The UPDATE...WHERE CURRENT OF
statement can only be run on the server, for example in an interactive psql session or a script. Language extensions such as PL/pgSQL do not have support for updatable cursors.
output_expression
An expression to be computed and returned by the UPDATE
command after each row is updated. The expression may use any column names of the table named by table_name or table(s) listed in FROM
. Write *
to return all columns.
output_name
A name to use for a returned column.
Outputs
On successful completion, an UPDATE
command returns a command tag of the form:
UPDATE <count>