Agregates in update?

From: Aleksey Dashevsky <postgres(at)luckynet(dot)co(dot)il>
To: pgsql-hackers(at)postgreSQL(dot)org, pgsql-questions(at)postgreSQL(dot)org
Subject: Agregates in update?
Date: 1998-04-10 10:31:02
Message-ID: Pine.LNX.3.96.SK.980410132057.13237B-100000@kesha.luckynet.co.il
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

hi, All!
Just installed PG 6.3.1 -- really great job, thank you guys!

But this morning I decided to play a bit with aggregate functions on
update and got a bit strange(for me, at least :-) result.
Here is an exmaple of what I did:
========================================
Let's create two simple tables
create table a (name text sum float);
create table b (name text ,val float);

--and then populate them with rows

insert into a values ('bob', 0.0);
insert into a values ('john', 0.0 );
insert into a values ('mike', 0.0);

insert into b values ('bob', 1.0);
insert into b values ('bob', 2.0);
insert into b values ('bob', 3.0);
insert into b values ('john', 4.0);
insert into b values ('john', 5.0);
insert into b values ('john', 6.0);
insert into b values ('mike', 670);
insert into b values ('mike', 8.0);
insert into b values ('mike', 9.0);

--now I want to update "sum" fields of table a in a way they will conatain
--sums of field "val" from table b groupped by name
--and use for this following query:
update a set sum=sum(b.val) where name=b.name ;
--Now
select * from a;
-- gives me:
name|sum
----+---
john| 0
mike| 0
bob |708
(3 rows)

===================
Now I'm wondering if there is reall problem in PostgreSQL or my
misundersanding of something important in SQL.

I'm running Linux-2.0.30(Slackware) and gcc-2.7.2.3

Thank you,
Aleksey.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message t-ishii 1998-04-10 10:45:44 Re: [HACKERS] subselect and optimizer
Previous Message Peter T Mount 1998-04-10 10:06:34 Re: [INTERFACES] New pg_type for large object