Thread: How to efficiently update many records at once
I have two tables with different information indexed by a unique key.
I want to update the contents of one table if an entry exists in
a second table.
Some playing with explain suggests that the optimum strategy using
UPDATE is:
update table1 set x=1 from table2 where key in
(select key from table2 where table1.key=table2.key);
This *does work* but can double the size of the database (until
the next vacuum). Is there an efficient way to do this in situ?
The problem is that my database is 100GB and only have 132GB
of space.
Sorry if this is a SQL/DBMS FAQ . . . I couldn't find any guidance
in the books and newsgroups.
--Martin
===========================================================================
Martin Weinberg Phone: (413) 545-3821
Dept. of Physics and Astronomy FAX: (413) 545-2117/0648
530 Graduate Research Tower
University of Massachusetts
Amherst, MA 01003-4525
At 03:07 PM 11/18/1999 , Martin Weinberg wrote:
>I have two tables with different information indexed by a unique key.
>I want to update the contents of one table if an entry exists in
>a second table.
>
>Some playing with explain suggests that the optimum strategy using
>UPDATE is:
>
>update table1 set x=1 from table2 where key in
> (select key from table2 where table1.key=table2.key);
>
>This *does work* but can double the size of the database (until
>the next vacuum). Is there an efficient way to do this in situ?
>
>The problem is that my database is 100GB and only have 132GB
>of space.
Martin,
You could try using a simple Join clause:
update table1
set x=1
from table2
where table2.key = table1.key;
or the Exists clause:
update table1
set x=1
from table2
where exists (select * from table2 where table2.key = table1.key); (In this
one I'm not sure if the from table2 is needed in the update section.)
Have not had a chance to test these, sorry! Hope they help.
Beth :-)
_______________________________________________
/ Beth L Strohmayer / Software Engineer _____)
/ ITT Industries, Systems Division (_____|______________________
/ @ Naval Research Laboratory, Code 5542 | \
\ 4555 Overlook Ave. SW | Phone: (202) 404-3798 \
\ Washington, DC 20375 | Fax: (202) 404-7942 \
\_________________________________________| /
| strohmayer@itd.nrl.navy.mil /
|____________________________/
Beth, Thanks for the reply. I did test both of these and they work but more slowly. The behavior of the database is the same: updates appear to be appended to the database until the next vacuum. --M Beth Strohmayer wrote on Thu, 18 Nov 1999 15:59:42 EST >At 03:07 PM 11/18/1999 , Martin Weinberg wrote: >>I have two tables with different information indexed by a unique key. >>I want to update the contents of one table if an entry exists in >>a second table. >> >>Some playing with explain suggests that the optimum strategy using >>UPDATE is: >> >>update table1 set x=1 from table2 where key in >> (select key from table2 where table1.key=table2.key); >> >>This *does work* but can double the size of the database (until >>the next vacuum). Is there an efficient way to do this in situ? >> >>The problem is that my database is 100GB and only have 132GB >>of space. > >Martin, > >You could try using a simple Join clause: > >update table1 >set x=1 >from table2 >where table2.key = table1.key; > >or the Exists clause: > >update table1 >set x=1 >from table2 >where exists (select * from table2 where table2.key = table1.key); (In this >one I'm not sure if the from table2 is needed in the update section.) > >Have not had a chance to test these, sorry! Hope they help. > >Beth :-) > _______________________________________________ > / Beth L Strohmayer / Software Engineer _____) > / ITT Industries, Systems Division (_____|______________________ >/ @ Naval Research Laboratory, Code 5542 | \ >\ 4555 Overlook Ave. SW | Phone: (202) 404-3798 \ > \ Washington, DC 20375 | Fax: (202) 404-7942 \ > \_________________________________________| / > | strohmayer@itd.nrl.navy.mil / > |____________________________/ >