Re: [GENERAL] How to efficiently update many records at once

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(at)itd(dot)nrl(dot)navy(dot)mil /
|____________________________/