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

From: David Stanaway <david(at)stanaway(dot)net>
To: pgsql-sql <pgsql-sql(at)postgresql(dot)org>
Subject: UPDATE FROM problem, multiple updates of same row don't seem to work
Date: 2004-08-09 20:16:29
Message-ID: 1092082589.7197.3.camel@dmxnocws13.dialmex.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)stanaway(dot)net>

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Philippe Lang 2004-08-10 13:09:50 function expression in FROM may not refer to other relations of same query level
Previous Message Vlad Dimitriu 2004-08-09 17:48:08 Re: Exception handling from trigger