Re: Update with aggregate subquery?

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