Обсуждение: 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?