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

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.

Databases - Practical PostgreSQL
Previous Page Home Next Page

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