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

Re: Update with aggregate subquery?

From: "Josh Tolley" <eggyknap(at)gmail(dot)com>
To: "Steve Lefevre" <lefevre(dot)10(at)osu(dot)edu>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Update with aggregate subquery?
Date: 2007-07-05 14:35:14
Message-ID: (view raw or whole thread)
Lists: pgsql-novice
On 7/5/07, Steve Lefevre <lefevre(dot)10(at)osu(dot)edu> wrote:
> Hello all -
> I'm trying to find a way to simply some code, and I have an idea that I
> can't quite hatch. It might be too complex for SQL.
> I'm trying to do something like
> UPDATE first_table SET latitude = ( SELECT avg(lat) FROM another_table
> GROUP BY another_table.first_table_id WHERE another_table.first_table_id
> = )
> Basically I have to refer to the 'outside' table within the subselect --
> the '' in the subselect. Is this possible?
> The added wrinkle is that the table I am selecting from is the same
> table in a self join! Would that add any problems?

So first_table and another_table are really the same table, if I read
you correctly? It looks like you can do this (and get a much faster
execution) doing something like this:
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>        message can get through to the mailing list cleanly

UPDATE first_table f SET latitude = n.latitude FROM (SELECT
first_table_id, avg(latitude) FROM first_table GROUP BY
first_table_id) n WHERE n.first_table_id = f.first_table_id;

See for
more on UPDATE...FROM. Note that it's PostgreSQL-specific syntax, not
standard SQL.


In response to

pgsql-novice by date

Next:From: Raimon FernandezDate: 2007-07-05 18:34:25
Subject: subquery with more than one column
Previous:From: Steve LefevreDate: 2007-07-05 13:35:44
Subject: Update with aggregate subquery?

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