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.
If the WHERE clause is omitted, an UPDATE
statement will modify each of the values within the entire specified column. This is generally most useful when updating
columns with an expression rather than a constant value. When an expression is specified in the
SET clause, it is re-evaluated just before updating each row. Thus, each row is updated
to a value determined dynamically by the interpreted expression's value for each row. This is demonstrated in Example 4-54.
Example 4-54 demonstrates using an UPDATE statement on
the stock table's retail column. It uses a mathematical
expression to raise the retail price of each stocked book. The expression itself has several components, separated by
parentheses to enforce order of execution.
The (retail / cost) sub-expression determines the current profit margin of the
book, which is then incremented by one tenth with the + operator and a floating-point
constant of 0.1. The 0.1::numeric syntax explicitly casts the
floating point constant to a value of type numeric. This is necessary due to the result
of the division sub-expression returning a value of type numeric. Finally, this new
profit margin is multiplied by the base cost from the cost column, resulting in the new
price with which the retail column should be updated.
Example 4-54. Updating entire columns
booktown=# SELECT isbn, retail, cost
booktown-# FROM stock
booktown-# ORDER BY isbn ASC
booktown-# LIMIT 3;
isbn | retail | cost
------------+--------+-------
0385121679 | 36.95 | 29.00
039480001X | 32.95 | 30.00
0394800753 | 16.95 | 16.00
(3 rows)
booktown=# UPDATE stock
booktown-# SET retail =
booktown-# (cost * ((retail / cost) + 0.1::numeric));
UPDATE 16
booktown=# SELECT isbn, retail, cost
booktown-# FROM stock
booktown-# ORDER BY isbn ASC
booktown-# LIMIT 3;
isbn | retail | cost
------------+--------+-------
0385121679 | 39.85 | 29.00
039480001X | 35.95 | 30.00
0394800753 | 18.55 | 16.00
(3 rows)Since the UPDATE statement in Example 4-54 has no
WHERE clause, all rows within the stock table
are modified by this statement.
By separating assignment expressions in the SET clause with commas, you may
execute updates to several columns of a table in a single statement. Example 4-55
illustrates updating both the name and address column
of the publishers table for the Publisher with the id
of 113.
Example 4-55. Using UPDATE on several columns
booktown=# UPDATE publishers
booktown-# SET name = 'O\'Reilly & Associates',
booktown-# address = 'O\'Reilly & Associates, Inc. '
booktown-# || '101 Morris St, Sebastopol, CA 95472'
booktown-# WHERE id = 113;
UPDATE 1
booktown=# SELECT name, substr(address, 1, 40) || '...' AS short_address
booktown-# FROM publishers
booktown-# WHERE id = 113;
name | short_address
-----------------------+---------------------------------------------
O'Reilly & Associates | O'Reilly & Associates, Inc. 101 Morris S...
(1 row)The UPDATE statement in Example 4-55 shows both
the name and address columns assigned through
string constants. Notice that several backslashes within the string constants escape the input apostrophes. The
SELECT statement following the update verifies that the desired information was updated.
Example 4-55 also demonstrates the use of the ||
text concatenation operator, and the substr() function, in practical usage. The
address column is set with two string constants that are attached through the
|| operator in order to prevent the query from wrapping past the edge of the terminal.
The substr() function is then used in the SELECT
verification to prevent the output from wrapping. Each of these are used here to maintain readability of the output (of course, you would not want to
display only a substring of the address field if you were interested in verifying its complete contents).
PostgreSQL supports a powerful non-standard enhancement to the SQL UPDATE
statement in the form of the FROM clause. By using the
FROM clause, you can apply your knowledge of the SELECT
statement to draw input data from other existing data sets, such as tables, or sub-selects.
Example 4-56 uses an UPDATE statement in
conjunction with a FROM clause to modify the row data within the
stock table via the stock_backup table. The
WHERE clause describes the relationship between the table to be updated and
its source. Wherever the isbn column is found to match, the value in the
stock table is modified to the value from the previously populated
stock_backup table.
Example 4-56. Using UPDATE with several sources
booktown=# UPDATE stock
booktown-# SET retail = stock_backup.retail
booktown-# FROM stock_backup
booktown-# WHERE stock.isbn = stock_backup.isbn;
UPDATE 16
The FROM clause supports each of the JOIN syntax
options described in the Section called Retrieving Rows with SELECT," enabling a wide variety of update methods from existing
data sets. Further, as stated previously, sub-selects may be used as a data source to the
FROM clause, just as is possible with the SELECT
command.
