UPDATE

Name

UPDATE -- Replaces values of columns in a table

UPDATE table

SET col = expression [, ...]

[ FROM fromlist ]

[ WHERE condition ]

Inputs

table

The name of an existing table.

column

The name of a column in table.

expression

A valid expression or value to assign to column.

fromlist

A Postgres non-standard extension to allow columns from other tables to appear in the WHERE condition.

condition

Refer to the SELECT statement for a further description of the WHERE clause.

Outputs

UPDATE #

Message returned if successful. The # means the number of rows updated. If # is 0 no rows are updated.

Description

UPDATE changes the values of the columns specified for all rows which satisfy condition. Only the columns to be modified need appear as columns in the statement.

Array references use the same syntax found in SELECT. That is, either single array elements, a range of array elements or the entire array may be replaced with a single query.

You must have write access to the table in order to modify it, as well as read access to any table whose values are mentioned in the WHERE condition.

Usage

Change word "Drama" with "Dramatic" on column kind:

 

        UPDATE films 
        SET kind = 'Dramatic' 
        WHERE kind = 'Drama';
        SELECT * 
        FROM films 
        WHERE kind = 'Dramatic' OR kind = 'Drama';
         code  |     title     | did | date_prod  |   kind   | len
        -------+---------------+-----+------------+----------+-------
         BL101 | The Third Man | 101 | 1949-12-23 | Dramatic | 01:44
         P_302 | Becket        | 103 | 1964-02-03 | Dramatic | 02:28
         M_401 | War and Peace | 104 | 1967-02-12 | Dramatic | 05:57
         T_601 | Yojimbo       | 106 | 1961-06-16 | Dramatic | 01:50
         DA101 | Das Boot      | 110 | 1981-11-11 | Dramatic | 02:29  

Compatibility

SQL92

SQL92 defines a different syntax for the positioned UPDATE statement:

 

        UPDATE table 
        SET column = expression [, ...] 
        WHERE CURRENT OF cursor  

where cursor identifies an open cursor.