Обсуждение: UPDATE FROM problem, multiple updates of same row don't seem to work

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 isid | flag
----+------ 1 |    7 2 |    5


Is there a way around this so that I can get the desired output?

-- 
David Stanaway <david@stanaway.net>

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.

On Wed, Aug 11, 2004 at 20:50:28 -0500, David Stanaway <david@stanaway.net> wrote:
> 
> I had thought about that, but this is a simpler case of what I need to 
> do. The operations for each column in the update are dependent on the 
> current and new values of each row being merged.
> 
> Currently I am doing this with a cursor which is very slow! How about a 
> trigger on update? Or would this suffer the same behavior that I am 
> seeing with UPDATE FROM with a 1-many join?

There is a good chance that triggers would be faster since tablea seems
to reallly be a materialized view of an aggregate over tableb and doing
a delta calculation when a row changes is going to be faster than recalculating
the aggregate from scratch.

You won't have the same problem if you use a trigger as when you were
trying to calculate an aggregate using UPDATE.