From: | Robert Poor <rdpoor(at)gmail(dot)com> |
---|---|
To: | Maximilian Tyrtania <lists(at)contactking(dot)de> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: updating records in table A from selected records in table B [SOLVED] |
Date: | 2011-03-30 18:16:06 |
Message-ID: | AANLkTinUAsCe-5oApQQafteNryQpPAQ6ZfD2=ZvSc4cs@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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;
From | Date | Subject | |
---|---|---|---|
Next Message | mike lan | 2011-03-30 18:50:00 | migrate postgresql database from 8.3 to 8.4 version ? |
Previous Message | Robert Poor | 2011-03-30 16:22:56 | Re: updating records in table A from selected records in table B |