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.