Re: [HACKERS] Agregates in update?

Vadim wrote:
> Bruce Momjian wrote:
> >
> > Added to TODO list.
> > > update a set sum=sum(b.val) where name=b.name ;
>
> Is this in standards ???
> I thought that subselects should be used in such cases...
> And this is one of my plans for 6.4...
>
> Vadim

I tried this with Illustra:

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 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 ;
XL0002:schema b does not exist

The problem of course is that the query

update a set sum=sum(b.val) where name=b.name;

is as Vadim points out, not valid SQL. Probably we should return an error.
I am not especially thrilled with the message above about schemas, but I can
see how it got there as the parser tried to find something (in the absence of
a from list) to give meaning to the term 'b.*'.

-dg

David Gould dg(at)illustra(dot)com 510.628.3783 or 510.305.9468
Informix Software (No, really) 300 Lakeside Drive Oakland, CA 94612
"(Windows NT) version 5.0 will build on a proven system architecture
and incorporate tens of thousands of bug fixes from version 4.0."
-- <http://www.microsoft.com/y2k.asp?A=7&B=5>