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.
Example 4-53 demonstrates a simple UPDATE statement. It instructs
PostgreSQL to update the value in the stock table's
retail column with the floating-point constant value of 29.95. The
WHERE clause constrains any modifications to rows that match the criteria described
by it.
Example 4-53. A simple UPDATE
booktown=#
SELECT retail FROM stock
booktown-#
WHERE isbn = '0590445065';
retail
--------
23.95
(1 row)
booktown=#
UPDATE stock
booktown-#
SET retail = 25.95
booktown-#
WHERE isbn = '0590445065';
UPDATE 1
booktown=#
SELECT retail FROM stock
booktown-#
WHERE isbn = '0590445065';
retail
--------
25.95
(1 row)
The resultant UPDATE 1 message from Example 4-53 indicates that one
record was successfully updated. Even if the value that is modified is identical to the record previously stored, it is
considered an update, and the database files on disk are still modified as a result of the statement.
