[SQL] 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] update with multiple fields as aggregates
On Sat, 3 May 2008, Alexy Khrabrov <[EMAIL PROTECTED]> 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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
