On Mon, Aug 09, 2004 at 15:16:29 -0500, David Stanaway <[EMAIL PROTECTED]> wrote: > 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
The original value of tablea.flag for each id will be used here. So that only one of the tableb.flag values will be or'd in for each id. > 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? Write a custom aggregate function that does the or for you. ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
