From: | David Stanaway <david(at)stanaway(dot)net> |
---|---|
To: | pgsql-sql <pgsql-sql(at)postgresql(dot)org> |
Subject: | UPDATE FROM problem, multiple updates of same row don't seem to work |
Date: | 2004-08-09 20:16:29 |
Message-ID: | 1092082589.7197.3.camel@dmxnocws13.dialmex.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Here is an example:
CREATE TABLE tablea(
id int PRIMARY KEY,
flag int
);
CREATE TABLE tableb(
aid int REFERENCES tablea(id),
flag int
);
INSERT INTO tablea VALUES(1,0);
INSERT INTO tablea VALUES(2,0);
-- Flags for 1st row of tablea - When ORed, should be 7
INSERT INTO tableb VALUES(1,1);
INSERT INTO tableb VALUES(1,2);
INSERT INTO tableb VALUES(1,4);
-- Flags for 2nd row of tablea - When ORed, should be 5
INSERT INTO tableb VALUES(2,1);
INSERT INTO tableb VALUES(2,4);
UPDATE tablea
SET flag = tablea.flag | tableb.flag
FROM tableb
WHERE tablea.id = tableb.aid;
SELECT * from tablea;
id | flag
----+------
1 | 1
2 | 1
-- Desired output is
id | flag
----+------
1 | 7
2 | 5
Is there a way around this so that I can get the desired output?
--
David Stanaway <david(at)stanaway(dot)net>
From | Date | Subject | |
---|---|---|---|
Next Message | Philippe Lang | 2004-08-10 13:09:50 | function expression in FROM may not refer to other relations of same query level |
Previous Message | Vlad Dimitriu | 2004-08-09 17:48:08 | Re: Exception handling from trigger |