Practical PostgreSQL

Modifying Rows with UPDATE

Once data has been inserted into rows within the database, those rows can have one or more of their column values modified through use of the SQL UPDATE command. Column values may be updated either with constants, identifiers to other data sets, or expressions. They may apply to an entire column, or a subset of a column’s values through specified conditions. The UPDATE command uses the following syntax:

UPDATE [ ONLY ] table SET
       column = expression [, ...]
       [ FROM source ]
       [ WHERE condition ]

UPDATE [ ONLY ] table

The ONLY keyword may be used to indicate that only the table table should be updated, and none of its sub-tables. This is only relevant if table is inherited by any other tables.

SET column = expression [, ...]

The required SET clause is followed by an update expression for each column name that needs to have its values modified, separated by commas. This expression is always of the form column = expression, where column is the name of the column to be updated (which may not be aliased, or dot-notated), and where expression describes the new value to be inserted into the column.

FROM source

The FROM clause is a non-standard PostgreSQL extension that allows table columns from other data sets to update a column’s value.

WHERE condition

The WHERE clause describes the condition upon which a row in table will be updated. If unspecified, all values in column will be modified. This may be used to qualify sources in the FROM clause, as you would in a SELECT statement. ...