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@itd.nrl.navy.mil /
                                             |____________________________/