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

Search Discussions

  • Maximilian Tyrtania at Mar 30, 2011 at 5:46 am

    ... 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
  • Robert Poor at Mar 30, 2011 at 4:31 pm
    @Maximilian:
    On Tue, Mar 29, 2011 at 22:46, Maximilian Tyrtania 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?
  • Robert Poor at Mar 30, 2011 at 6:16 pm
    I with gentle nudges from Maximilian and Jim Woodworth, I now
    understand how to update individual records in table A from matching
    records in table B. It turns out that no subquery is needed. The
    full postgreSQL script follows.

    [As an aside, the PostgreSQL documentation could be improved by
    including an example of UPDATE ... FROM to avoid this kind of
    question!]

    - rdp

    DROP TABLE "table_as";
    CREATE TABLE "table_as" ("id" serial primary key, "key" integer,
    "value" float) ;
    DROP TABLE "table_bs";
    CREATE TABLE "table_bs" ("id" serial primary key, "key" integer,
    "value" float) ;

    INSERT INTO "table_as" ("key", "value") VALUES (1, 1.0), (2, 2.0),
    (3, 3.0);
    INSERT INTO "table_bs" ("key", "value") VALUES (1, 101.0), (2, 102.0),
    (4, 104.0);

    SELECT 'BEFORE UPDATE';
    SELECT * FROM "table_as" ORDER BY id;

    -- update selected rows of table_as from table_bs (where key=1, 2 but not 3)
    UPDATE table_as SET value = table_bs.value
    FROM table_bs
    WHERE table_as.key = table_bs.key;

    SELECT 'AFTER UPDATE';
    SELECT * FROM "table_as" ORDER BY id;

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedMar 29, '11 at 8:42p
activeMar 30, '11 at 6:16p
posts4
users2
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase