Re: updating records in table A from selected records in table B - Mailing list pgsql-novice

@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?