Обсуждение: 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