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 x110Am 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