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). This uses the same syntax as the FROM clause of a SELECT statement for example, an alias for the table name can be specified.

from_itemĪ table expression allowing columns from other tables to appear in the WHERE condition and update expressions. The sub-query can refer to old values of the current row of the table being updated. 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 must yield no more than one row when executed. sub-SELECTĪ SELECT sub-query that produces as many output columns as are listed in the parenthesized column list preceding it. For a generated column, specifying this is permitted but merely specifies the normal behavior of computing the column from its generation expression. An identity column will be set to a new value generated by the associated sequence. Set the column to its default value (which will be NULL if no specific default expression has been assigned to it). The expression can use the old values of this and other columns in the table. expressionĪn expression to assign to the column. Do not include the table's name in the specification of a target column - for example, UPDATE table_name SET table_l = 1 is invalid. The column name can be qualified with a subfield name or array subscript, if needed. The name of a column in the table named by table_name. For example, given UPDATE foo AS f, the remainder of the UPDATE statement must refer to this table as f not foo. When an alias is provided, it completely hides the actual name of the table. aliasĪ substitute name for the target table. Optionally, * can be specified after the table name to explicitly indicate that descendant tables are included.

If ONLY is not specified, matching rows are also updated in any tables inheriting from the named table. If ONLY is specified before the table name, matching rows are updated in the named table only. The name (optionally schema-qualified) of the table to update. The WITH clause allows you to specify one or more subqueries that can be referenced by name in the UPDATE query. You must also have the SELECT privilege on any column whose values are read in the expressions or condition. You must have the UPDATE privilege on the table, or at least on the column(s) that are listed to be updated. The syntax of the RETURNING list is identical to that of the output list of SELECT. The new (post-update) values of the table's columns are used. Any expression using the table's columns, and/or columns of other tables mentioned in FROM, can be computed.

The optional RETURNING clause causes UPDATE to compute and return value(s) based on each row actually updated. Which technique is more appropriate depends on the specific circumstances. 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. Only the columns to be modified need be mentioned in the SET clause columns not explicitly modified retain their previous values. UPDATE changes the values of the specified columns in all rows that satisfy the condition.

Sql update postgresql