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

Re: update with multiple fields as aggregates

From: Volkan YAZICI <yazicivo(at)ttmail(dot)com>
To: Alexy Khrabrov <deliverable(at)gmail(dot)com>
Cc: sql pgsql <pgsql-sql(at)postgresql(dot)org>
Subject: Re: update with multiple fields as aggregates
Date: 2008-05-03 14:36:49
Message-ID: 87tzhffo8u.fsf@alamut.mobiliz.com.tr (view raw or flat)
Thread:
Lists: pgsql-sql
On Sat, 3 May 2008, Alexy Khrabrov <deliverable(at)gmail(dot)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.

In response to

pgsql-sql by date

Next:From: seilikiDate: 2008-05-04 16:28:04
Subject: LEFT OUTER JOIN question
Previous:From: Alexy KhrabrovDate: 2008-05-03 08:05:07
Subject: update with multiple fields as aggregates

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