Subject: UPDATE FROM problem, multiple updates of same row don't seem to work
From: David Stanaway <david@stanaway.net>
To: pgsql-sql <pgsql-sql@postgresql.org>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: Mon, 09 Aug 2004 15:16:29 -0500
Message-Id: <1092082589.7197.3.camel@dmxnocws13.dialmex.net>
Mime-Version: 1.0

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>