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@illustra.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>