PostgreSQL 8.0.0rc2 Documentation
Prev | Fast Backward |  | Fast Forward | Next

# UPDATE

## Name

UPDATE -- update rows of a table

## Synopsis

    UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...]
        [ FROM fromlist ]
        [ WHERE condition ]

## Description

`UPDATE` changes the values of the specified columns in all rows that satisfy the condition. Only the columns to be modified need be mentioned in the `SET` clause; columns not explicitly modified retain their previous values.

By default, `UPDATE` will update rows in the specified table and all its subtables. If you wish to only update the specific table mentioned, you must use the `ONLY` clause.

There are two ways to modify a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the `FROM` clause. Which technique is more appropriate depends on the specific circumstances.

You must have the `UPDATE` privilege on the table to update it, as well as the `SELECT` privilege to any table whose values are read in the expressions or condition.

## Parameters

table

  The name (optionally schema-qualified) of the table to update.

column

  The name of a column in table. The column name can be qualified with a subfield name or array subscript, if needed.

expression

  An expression to assign to the column. The expression may use the old values of this and other columns in the table.

`DEFAULT`

  Set the column to its default value (which will be NULL if no specific default expression has been assigned to it).

fromlist

  A list of table expressions, allowing columns from other tables to appear in the `WHERE` condition and the update expressions. This is similar to the list of tables that can be specified in the FROM Clause of a `SELECT` statement; for example, an alias for the table name can be specified.

condition

  An expression that returns a value of type `boolean`. Only rows for which this expression returns `true` will be updated.

## Outputs

On successful completion, an `UPDATE` command returns a command tag of the form

    UPDATE count

The count is the number of rows updated. If count is 0, no rows matched the condition (this is not considered an error).

## Examples

Change the word `Drama` to `Dramatic` in the column `kind` of the table `films`:

    UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';

Adjust temperature entries and reset precipitation to its default value in one row of the table `weather`:

    UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
      WHERE city = 'San Francisco' AND date = '2003-07-03';

Increment the sales count of the salesperson who manages the account for Acme Corporation, using the `FROM` clause syntax:

    UPDATE employees SET sales_count = sales_count + 1 FROM accounts
      WHERE accounts.name = 'Acme Corporation'
      AND employees.id = accounts.sales_person;

Perform the same operation, using a sub-select in the `WHERE` clause:

    UPDATE employees SET sales_count = sales_count + 1 WHERE id =
      (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation');

Attempt to insert a new stock item along with the quantity of stock. If the item exists, update the stock count of the existing item. To do this, use savepoints.

    BEGIN;
    SAVEPOINT sp1;
    INSERT INTO wines VALUES('Chateau Lafite 2003', '24');
    -- Check for unique violation on name
    ROLLBACK TO sp1;
    UPDATE wines SET stock = stock + 24 WHERE winename='Chateau Lafite 2003';
    COMMIT;

## Compatibility

This command conforms to the SQL standard. The `FROM` clause is a PostgreSQL extension.

Prev | Home | Next
UNLISTEN | Up | VACUUM