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