Practical PostgreSQL

Update

Modifies the values of column data within a table.

Synopsis

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

Parameters

ONLY

The optional ONLY keyword indicates to only update the specified table (and not its inheriting child tables, if it has any).

table

The name of an existing table to update.

column

The name of a column to update in the table you specified.

expression

An expression or value that you want assigned to the specified column.

fromlist

A valid table, view, or other from_item as defined in the reference entry titled “SELECT.” A PostgreSQL extension of the UPDATE command is the ability to use column values from other tables within the WHERE condition; to do this correctly, you must use this parameter to list the tables from which you will be pulling column values.

condition

The WHERE condition for UPDATE to use when determining what rows are to be updated. This can be any valid expression resulting in a value of type boolean.

Results

UPDATE count

The message returned when an UPDATE was successful. The count will actually be the number of rows that were modified as a result of the UPDATE. For example, if count is zero, it means that no rows were updated.

ERROR: Relation 'table' does not exist

The error returned if table is not a table in the connected database.

ERROR: Relation 'table' does not have attribute 'column'

The error returned if a column that does not exist in the table is used in the SET clause.

ERROR: Cannot update a view without ...