From: Bruno Wolff III <bruno@wolff.to>
To: David Stanaway <david@stanaway.net>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: UPDATE FROM problem, multiple updates of same row don't seem to work
Date: Wed, 11 Aug 2004 18:52:41 -0500
Message-ID: <20040811235241.GB19680@wolff.to> (raw)
In-Reply-To: <1092082589.7197.3.camel@dmxnocws13.dialmex.net>
References: <1092082589.7197.3.camel@dmxnocws13.dialmex.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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, david@stanaway.net
Subject: Re: UPDATE FROM problem, multiple updates of same row don't seem to work
In-Reply-To: <20040811235241.GB19680@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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