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
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
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

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

Databases - Practical PostgreSQL
Previous Page Home Next Page

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