UPDATE

Now that you've seen a number of ways to view your data, let's see how to modify (and delete) existing data.

The UPDATE command modifies data in one or more rows. The general form of the UPDATE command is

UPDATE table SET column = expression [, ...] [WHERE condition]

Using the UPDATE command is straightforward: The WHERE clause (if present) determines which rows will be updated and the SET clause determines which columns will be updated (and the new values).

You might have noticed in earlier examples that one of the tapes had a duration of '4 days, 01:36'that's obviously a mistake. You can correct this problem with the UPDATE command as follows:

movies=# UPDATE tapes SET duration = '4 hours 36 minutes'
movies-# WHERE tape_id = 'OW-42201';
UPDATE 1

movies=# SELECT * FROM tapes;
 tape_id | title | duration
----------+----------------------+----------
 AB-12345 | The Godfather |
 AB-67472 | The Godfather |
 MC-68873 | Casablanca |
 OW-41221 | Citizen Kane |
 AH-54706 | Rear Window |
 OW-42200 | Sly | 01:36
 KJ-03335 | American Citizen, An |
 OW-42201 | Stone Cold | 04:36
(8 rows)

Using the UPDATE command, you can update all the rows in the table, a single row, or a set of rowsit all depends on the WHERE clause. The SET clause in this example updates a single column in all the rows that satisfy the WHERE clause. If you want to update multiple columns, list each assignment, separated by commas:

movies=# UPDATE tapes
movies-# SET duration = '1 hour 52 minutes', title = 'Stone Cold'
movies-# WHERE tape_id = 'OW-42201';
UPDATE 1
movies=# SELECT * FROM tapes;
 tape_id | title | duration
----------+----------------------+----------
 AB-12345 | The Godfather |
 AB-67472 | The Godfather |
 MC-68873 | Casablanca |
 OW-41221 | Citizen Kane |
 AH-54706 | Rear Window |
 OW-42200 | Sly | 01:36
 KJ-03335 | American Citizen, An |
 OW-42201 | Stone Cold | 01:52
(8 rows)

The UPDATE statement displays the number of rows that were modified. The following UPDATE will modify three of the seven rows in the customers table:

movies=# SELECT * FROM customers;

 customer_id | customer_name | phone | birth_date | balance
-------------+----------------------+----------+------------+---------
 3 | Panky, Henry | 555-1221 | 1968-01-21 | 0.00
 1 | Jones, Henry | 555-1212 | 1970-10-10 | 0.00
 4 | Wonderland, Alice N. | 555-1122 | 1969-03-05 | 3.00
 2 | Rubin, William | 555-2211 | 1972-07-10 | 15.00
 5 | Funkmaster, Freddy | 555-FUNK | |
 7 | Gull, Jonathon LC | 555-1111 | 1984-02-05 |
 8 | Grumby, Jonas | 555-2222 | 1984-02-21 |
(7 rows)

movies=# UPDATE customers
movies-# SET balance = 0
movies-# WHERE balance IS NULL;
UPDATE 3
movies=# SELECT * FROM customers;

 customer_id | customer_name | phone | birth_date | balance
-------------+----------------------+----------+------------+---------
 3 | Panky, Henry | 555-1221 | 1968-01-21 | 0.00
 1 | Jones, Henry | 555-1212 | 1970-10-10 | 0.00
 4 | Wonderland, Alice N. | 555-1122 | 1969-03-05 | 3.00
 2 | Rubin, William | 555-2211 | 1972-07-10 | 15.00
 5 | Funkmaster, Freddy | 555-FUNK | | 0.00
 7 | Gull, Jonathon LC | 555-1111 | 1984-02-05 | 0.00
 8 | Grumby, Jonas | 555-2222 | 1984-02-21 | 0.00
(7 rows)


Part I: General PostgreSQL Use

Introduction to PostgreSQL and SQL

Working with Data in PostgreSQL

PostgreSQL SQL Syntax and Use

Performance

Part II: Programming with PostgreSQL

Introduction to PostgreSQL Programming

Extending PostgreSQL

PL/pgSQL

The PostgreSQL C APIlibpq

A Simpler C APIlibpgeasy

The New PostgreSQL C++ APIlibpqxx

Embedding SQL Commands in C Programsecpg

Using PostgreSQL from an ODBC Client Application

Using PostgreSQL from a Java Client Application

Using PostgreSQL with Perl

Using PostgreSQL with PHP

Using PostgreSQL with Tcl and Tcl/Tk

Using PostgreSQL with Python

Npgsql: The .NET Data Provider

Other Useful Programming Tools

Part III: PostgreSQL Administration

Introduction to PostgreSQL Administration

PostgreSQL Administration

Internationalization and Localization

Security

Replicating PostgreSQL Data with Slony

Contributed Modules

Index



PostgreSQL(c) The comprehensive guide to building, programming, and administering PostgreSQL databases
PostgreSQL(c) The comprehensive guide to building, programming, and administering PostgreSQL databases
ISBN: 735712573
EAN: N/A
Year: 2004
Pages: 261

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net