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

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;