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: e7e0a2570707050735m18bd12a5q9c0daa8469d750d7@mail.gmail.com (view raw or flat)
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
> = first_table.id )
>
> Basically I have to refer to the 'outside' table within the subselect --
> the 'first_table.id' 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 http://www.postgresql.org/docs/current/static/sql-update.html for
more on UPDATE...FROM. Note that it's PostgreSQL-specific syntax, not
standard SQL.

-Josh

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-2014 The PostgreSQL Global Development Group