Re: UPDATE FROM problem, multiple updates of same row don't seem to work

On Mon, Aug 09, 2004 at 15:16:29 -0500, David Stanaway <david@stanaway.net> 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.