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