Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




Databases - Practical PostgreSQL
Previous Page Home Next Page

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

UPDATE publishers

       SET name = 'O\'Reilly & Associates',

           address = 'O\'Reilly & Associates, Inc. '

                  || '101 Morris St, Sebastopol, CA 95472'

       WHERE id = 113;

SELECT name, substr(address, 1, 40) || '...' AS short_address

       FROM publishers

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

Databases - Practical PostgreSQL
Previous Page Home Next Page

  Published under the terms of the Open Publication License Design by Interspire