You appear to have accidentally just replied to me.
On Thu, Oct 16, 2003 at 12:48:35 -0400,
Mike Leahy <mgleahy@fes.uwaterloo.ca> wrote:
> Okay...that seems to work. However, there are two things that are causing
> me problems. First, if I use the statement exactly as you have it in your
> reply I get 'ERROR: parser: parse error at or near "." at character 15' -
> it doesn't seem to like the 'a.' before the first reference to the column
> being named. It's clearly nothing serious - I suppose it's redundant to put
> it there anyway, since we've already indicated we're updating the columns in
> table 'a' to begin with.
That was my mistake. A table name there makes no sense since as you noted
the column has to be in the table being updated.
> Second, update I'm trying to run is about 20000 records (both in the table
> being updated, and in the joined query). When I run this update, there is
> the usual length of time to run the query itself, then when it begins
> updating my processor sits at 100% for about 15 minutes (with a 2.4 GHz
> processor). Is this normal, or is my update statement structured poorly
> somehow? I tried dumping the query to a table rather than executing it
> within the update statement, and that didn't make much difference (example
> below).
An explain analyze would probably be useful to see.
> update tbl_ind_manzanas set poblacion = c.thecount from (select cod_manzana,
> thecount from tbl_ind_manzanas left join tmp_query on
> tbl_ind_manzanas.cod_manzana = tmp_query.cod_manz) as c where
> tbl_ind_manzanas.cod_manzana = c.cod_manzana;
>
> Can anyone suggest how this might possibly be improved so that it isn't so
> computationally intensive?
You probably want want to create an index on tmp_query (cod_manz) (and of
course (tbl_ind_manzanas (cod_manzana)) so that you don't need to do a sort
or nestloop to do the left join.
>
> At any rate...it seems to work fine despite the length of time to execute.
> Thanks for the help Bruno.
>
> Mike
>
>
> -----Original Message-----
> From: Bruno Wolff III [mailto:bruno@wolff.to]
> Sent: October 16, 2003 10:30 AM
> To: Mike Leahy
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] UPDATE table to a joined query...
>
> On Wed, Oct 15, 2003 at 22:39:39 -0400,
> Mike Leahy <mgleahy@fes.uwaterloo.ca> wrote:
> > Hello all,
> >
> > This question is related to updating tables - is there any way to
> calculate or
> > update the values in a column in a table to the values in a field produced
> by
> > a query result? An example of what I'm trying to do is below:
> >
> > update (tbl_ind_mananas LEFT JOIN (select count(*) as count, (dubicacion
> ||
> > zona || manzana) as cod_manzana from tbl_censo_poblacion_1993 group by
> > dubicacion, zona, manzana) tbl1 on relacion = cod_manzana) as tbl2 set
> > poblacion = count;
> >
> > Basically I have a table (tbl_ind_manzanas) with a unique code (relacion)
> that
> > can be linked to a field in the query result (cod_manzana). I want to
> update
> > a field in the table with the count(*) result in the query. The update
> > statement (as I have attempted it above) doesn't work...it seems that I
> can do
> > nothing but directly update the values in a table. As soon as I try to
> supply
> > anything other than just a table to the update statement, it doesn't like
> it.
> >
> > I'm sure others have experience with this issue...it seems to me that
> there
> > would be many cases where such an approach would be useful. I'm hoping
> > there's another method that I might be able to use that could accomplish
> > essentially the same result. Any suggestions are greatly appreciated.
>
> A similar question was asked in the last week.
>
> You want to do the join in the from item list and than join the table
> being updated to the join from the from item list in the where clause.
>
> Something like:
> update a set a.count = c.count from (select a left join b) as c
> where a.id = c.id;
>