From: David Stanaway <david@stanaway.net>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: UPDATE FROM problem, multiple updates of same row don't seem to work
Date: Mon, 09 Aug 2004 15:16:29 -0500
Message-ID: <1092082589.7197.3.camel@dmxnocws13.dialmex.net> (raw)
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@stanaway.net>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: david@stanaway.net
Subject: Re: UPDATE FROM problem, multiple updates of same row don't seem to work
In-Reply-To: <1092082589.7197.3.camel@dmxnocws13.dialmex.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox