Update with aggregate subquery?

From: Steve Lefevre <lefevre(dot)10(at)osu(dot)edu>
To: pgsql-novice(at)postgresql(dot)org
Subject: Update with aggregate subquery?
Date: 2007-07-05 13:35:44
Message-ID: 468CF3B0.10309@osu.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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?

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Josh Tolley 2007-07-05 14:35:14 Re: Update with aggregate subquery?
Previous Message mike lane 2007-07-05 10:33:15 how to get pg_dump in postgesql 8.2 to create inserts with E'....' for back slashes