Practical PostgreSQL

Modifying Rows with UPDATE

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.

Updating Entire Columns

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 floatingpoint 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.

Updating Several Columns

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).

Updating from Several Sources

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 titled 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.