pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUPDATE FROM problem, multiple updates of same row don't seem to work
3+ messages / 2 participants
[nested] [flat]
* UPDATE FROM problem, multiple updates of same row don't seem to work
@ 2004-08-09 20:16 David Stanaway <david@stanaway.net>
0 siblings, 1 reply; 3+ messages in thread
From: David Stanaway @ 2004-08-09 20:16 UTC (permalink / raw)
To: pgsql-sql
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>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: UPDATE FROM problem, multiple updates of same row don't seem to work
@ 2004-08-11 23:52 Bruno Wolff III <bruno@wolff.to>
parent: David Stanaway <david@stanaway.net>
0 siblings, 1 reply; 3+ messages in thread
From: Bruno Wolff III @ 2004-08-11 23:52 UTC (permalink / raw)
To: David Stanaway <david@stanaway.net>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: UPDATE FROM problem, multiple updates of same row don't seem to work
@ 2004-08-12 02:27 Bruno Wolff III <bruno@wolff.to>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 3+ messages in thread
From: Bruno Wolff III @ 2004-08-12 02:27 UTC (permalink / raw)
To: David Stanaway <david@stanaway.net>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-08-12 02:27 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-09 20:16 UPDATE FROM problem, multiple updates of same row don't seem to work David Stanaway <david@stanaway.net>
2004-08-11 23:52 ` Bruno Wolff III <bruno@wolff.to>
2004-08-12 02:27 ` Bruno Wolff III <bruno@wolff.to>
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