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

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 (view raw or flat)
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

pgsql-novice by date

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

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