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

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 /
>                                             |____________________________/
>