Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Agregates in update?

From: dg(at)illustra(dot)com (David Gould)
To: vadim(at)sable(dot)krasnoyarsk(dot)su (Vadim B(dot) Mikheev)
Cc: maillist(at)candle(dot)pha(dot)pa(dot)us, postgres(at)luckynet(dot)co(dot)il, pgsql-hackers(at)postgreSQL(dot)org, pgsql-questions(at)postgreSQL(dot)org
Subject: Re: [HACKERS] Agregates in update?
Date: 1998-04-27 08:49:10
Message-ID: 9804270849.AA15338@hawk.illustra.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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>



In response to

pgsql-hackers by date

Next:From: Gran ThyniDate: 1998-04-27 09:11:37
Subject: Re: [QUESTIONS] PostgreSQL and LDAP ?
Previous:From: Andreas ZeugswetterDate: 1998-04-27 08:41:32
Subject: Re: [HACKERS] Re: [INTERFACES] retrieving varchar size

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group