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