updating records in table A from selected records in table B

From: Robert Poor <rdpoor(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: updating records in table A from selected records in table B
Date: 2011-03-29 20:41:55
Message-ID: AANLkTi=7XyAb2YyxDtPgJMb1_ofAQC6wQpAe1GPrsdBu@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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...

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message aaronenabs 2011-03-30 01:22:47 pg_dumpall
Previous Message Robert Poor 2011-03-29 17:18:17 Re: inserting a NULL timestamp