Обсуждение: slow update

Hi,

i need to update a table in a database and i already managed it to
copy new data in a temproary table with 'copy' command very quick.

what i have is
CREATE TABLE tmp  (id int4, val1 int4, val2 int4);
CREATE TABLE real (id int4 PRIMARY KEY, val1 int4, val2 int4);

not all id s in real have appropiate values in temp.
now i just want to merge these tables and  i tried:

UPDATE real
   SET val1=(SELECT val1 FROM temp WHERE temp.id = real.id ),
       val2=(SELECT val2 FROM temp WHERE temp.id = real.id )
WHERE  id IN (SELECT temp.id FROM temp);

update is taking very long time. there is an index on real but not on
the columns which i update and theer are no triggers at all.
but there are many, many rows in real.

Can anybody give me a hint to a faster way updating the data??

it looks like a very easy task for the database just to merge to
tables, but maybe its not?

kind regards,
janning

On Fri, Jul 26, 2002 at 03:14:37PM +0200, Janning Vygen wrote:
>
> UPDATE real
>    SET val1=(SELECT val1 FROM temp WHERE temp.id = real.id ),
>        val2=(SELECT val2 FROM temp WHERE temp.id = real.id )
> WHERE  id IN (SELECT temp.id FROM temp);

Ick.  IN is a well-known dog in Postgres.  Try this:

UPDATE real
    SET val1=temp.val1,val2=temp.val2
    WHERE id=temp.id;

A

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

Am Freitag, 26. Juli 2002 16:46 schrieb Andrew Sullivan:
> On Fri, Jul 26, 2002 at 03:14:37PM +0200, Janning Vygen wrote:
> > UPDATE real
> >    SET val1=(SELECT val1 FROM temp WHERE temp.id = real.id ),
> >        val2=(SELECT val2 FROM temp WHERE temp.id = real.id )
> > WHERE  id IN (SELECT temp.id FROM temp);
>
> Ick.  IN is a well-known dog in Postgres.  Try this:
>
> UPDATE real
>     SET val1=temp.val1,val2=temp.val2
>     WHERE id=temp.id;

oh thanks a lot! this is MUCH faster! but it occurs another problem...
.. but this should be posted in another thread...

kind regards
janning