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
Answertopia.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 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 floating-point 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.

Databases - Practical PostgreSQL
Previous Page Home Next Page

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