UPDATE [ ONLY ] table SET col = expression [, ...]
[ FROM fromlist ]
[ WHERE condition ]
The name (optionally schema-qualified) of an existing
table. If ONLY is specified,
only that table is updated. If ONLY is not specified, the table and all
its descendant tables (if any) are updated. * can be appended to the table name to
indicate that descendant tables are to be scanned, but in
the current version, this is the default behavior. (In
releases before 7.1, ONLY was
the default behavior.) The default can be altered by
changing the SQL_INHERITANCE
configuration option.
The name of a column in table.
A valid expression or value to assign to column.
A PostgreSQL non-standard extension to allow columns from other tables to appear in the WHERE condition.
Refer to the SELECT statement for a further description of the WHERE clause.
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.
By default UPDATE will update tuples in the table specified and all its sub-tables. If you wish to only update the specific table mentioned, you should use the ONLY clause.
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
A lot of people don't know this, but the where expression allows subselects. This would generally be a better way to write back data constructed from a multiple table query than to do an iterative loop in code. IMHO, this is a much more genrally useful application of subselects than complex queries. (select statements can be written to avoid subselects, update statements can't). Contrast mysql.
Example:
update b set value = 'changed' where b.aid in (select id from a where afield = 'filter_condition');
A more automatic approach would be to use views (which requires learning & dealing with the rule system) or triggers/stored procedures. I generally use views and sps but not triggers. You just can't beat an update for simplicity though. My point is that doing an interative loop in php or some other client language is usually not the best approach.
It should probably be noted here that the FROM clause also makes the foreign tables available to the expression part of the SET clause.
update-from example that copies one column between tables using another as key.
update
table_a
set
column_1 = table_b.column_1
from
table_b
where
table_a.column_2 = table_b.column_2;
HTH.