Thread: update with multiple fields as aggregates

I need to fill two columns of a Rats table from an Offset1 table,  
where for each Id row in Rats we need to fill an average offset and  
the sum of all offset from Offset1 with the same Id.  I can create a  
derivative table like this:

create table ofrats as (select customer_id as cid,avg(o),sum(o) from  
offset1 group by cid);

But if I want to insert the two values into the Rats directly, I get  
an error:

netflix=> update rats r1 set of1=s.ao, sumof1=s.so from (select avg(o)  
as ao,sum(o) as so from rats,offset1 o1 where o1.customer_id=r1.id  
group by id) as s;
ERROR:  subquery in FROM cannot refer to other relations of same query  
level

-- is there a way to formulate the subquery for Update properly here  
so it's as efficient as the transfer table above?  Currently I have to  
create that auxiliary table and then transfer values into Rats via Id,  
then drop the table -- is it an idiom too or there's a better way to  
do it?

Cheers,
Alexy

On Sat, 3 May 2008, Alexy Khrabrov <deliverable@gmail.com> writes:
> I need to fill two columns of a Rats table from an Offset1 table,
> where for each Id row in Rats we need to fill an average offset and
> the sum of all offset from Offset1 with the same Id.  I can create a
> derivative table like this:
>
> create table ofrats as (select customer_id as cid,avg(o),sum(o) from
> offset1 group by cid);
>
> But if I want to insert the two values into the Rats directly, I get
> an error:
>
> netflix=> update rats r1 set of1=s.ao, sumof1=s.so from (select avg(o)
> as ao,sum(o) as so from rats,offset1 o1 where o1.customer_id=r1.id
> group by id) as s;
> ERROR:  subquery in FROM cannot refer to other relations of same query
> level

I didn't try but, here is my 2 cents:
 UPDATE rats    SET of1 = tmp.of1,        sumof1 = tmp.sumof1   FROM (SELECT id, AVG(o) AS of1, SUM(o) AS sumof1
  FROM rats,                offset1       GROUP BY id)     AS tmp  WHERE tmp.id = rats.id;
 


Regards.