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.
