UPDATE table to a joined query...

[Mike Leahy]

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.

Kind regards,
Mike
----------------------------------------
This mail sent through www.mywaterloo.ca

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Bruno Wolff III]

On Wed, Oct 15, 2003 at 22:39:39 -0400,
Mike Leahy <mg*****@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;

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Bruno Wolff III]

You appear to have accidentally just replied to me.

On Thu, Oct 16, 2003 at 12:48:35 -0400,
Mike Leahy <mg*****@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:br***@wolff.to]
Sent: October 16, 2003 10:30 AM
To: Mike Leahy
Cc: pg***********@postgresql.org
Subject: Re: [GENERAL] UPDATE table to a joined query...

On Wed, Oct 15, 2003 at 22:39:39 -0400,
Mike Leahy <mg*****@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;

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match