X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2273414B2878 for ; Wed, 11 Aug 2004 20:44:39 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 22702-08 for ; Wed, 11 Aug 2004 23:44:30 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 359C614B286A for ; Wed, 11 Aug 2004 20:44:29 -0300 (ADT) Received: (qmail 20124 invoked by uid 500); 11 Aug 2004 23:52:41 -0000 Date: Wed, 11 Aug 2004 18:52:41 -0500 From: Bruno Wolff III To: David Stanaway Cc: pgsql-sql Subject: Re: UPDATE FROM problem, multiple updates of same row don't seem to work Message-ID: <20040811235241.GB19680@wolff.to> Mail-Followup-To: David Stanaway , pgsql-sql References: <1092082589.7197.3.camel@dmxnocws13.dialmex.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1092082589.7197.3.camel@dmxnocws13.dialmex.net> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200408/102 X-Sequence-Number: 18599 On Mon, Aug 09, 2004 at 15:16:29 -0500, David Stanaway 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.