Обсуждение: updating records in table A from selected records in table B

I've pored over the syntax for UPDATE but I think I'm missing something.

Assuming a schema such as:

      Column       |            Type             |
    Modifiers
--------------------+-----------------------------+------------------------------------------------------------
 id                 | integer                     | not null default
nextval('service_bills_id_seq'::regclass)
 fk                 | integer                     |
 start_time         | timestamp without time zone |
 quantity           | numeric(10,5)               |
 cost               | numeric(10,5)               |

Starting with the results from this query:

     SELECT candidates.quantity, candidates.cost
         FROM table_b AS candidates
INNER JOIN table_a AS incumbents
             ON incumbents.fk = candidates.fk
           AND incumbents.start_time = candidates.start_time

... is there a way to update quantity and cost fields in incumbents
with the matching records from candidates?  It seems that UPDATE is
designed only to update one record at a time...

> ... is there a way to update quantity and cost fields in incumbents
> with the matching records from candidates?  It seems that UPDATE is
> designed only to update one record at a time...

Oh, no, it is certainly possible to do what you want here. The usual trick is:

UPDATE sometable set somefield=somevalue where id in (select id from somecomplicatedsubquery)

Maximilian Tyrtania Software-Entwicklung
Dessauer Str. 6-7
10969 Berlin
http://www.contactking.de

@Maximilian:

On Tue, Mar 29, 2011 at 22:46, Maximilian Tyrtania <lists@contactking.de> wrote:
>> It seems that UPDATE is designed only to update one record at a time...
>
> Oh, no, it is certainly possible to do what you want here. The usual trick is:
>
> UPDATE sometable set somefield=somevalue where id in (select id from somecomplicatedsubquery)

I may be misreading your reply, but I get

  PGError: ERROR:  missing FROM-clause entry for table "candidate"

With the following query:

  UPDATE table_as SET incumbent.value = candidate.value
   WHERE id IN (    SELECT id
                      FROM table_b AS candidates
                INNER JOIN table_as AS incumbents
                        ON incumbents.key = candidate.key)

Is that what you meant?