Re: Best way for updating EVERY row in a large database?

In article <6hnf6t$1oqq$1_at_rtpnews.raleigh.ibm.com>, "Jim Morgan" <jimmorgan_at_csi.com> wrote:

> I've got an application that is run once every several months which resets
> several columns to zero in a table that contains > 3,000,000 rows. In order
> to take advantage of a variable commit frequency, I allow the frequency to
> be specified as a parameter when I start my program.
>
> What I am doing is opening a cursor which will select the key column of the
> entire table I need to update, fetch the rows one at a time, then issue a
> separate UPDATE statemement for each row I fetch from the cursor.
> snip

May I ask the obvious question?
Why is it necessary to fetch each row, and issue a separate statement for it, if all you are doing is resetting some column values to zero? Can you not just issue a single statement, viz:

update the_table
set col1 = 0, col2 = 0, etc
WHERE same_select_conditions

If the select conditions select all rows to be updated, but the specific columns to be updated for a given row depend on some other condition(s) in the row, then
you may be able to use a statement such as:

update the_table
set col1 = decode(condition1,value1,col1) -- col1 unchanged if condition1 false

   ,col2 = decode(condition2,value2,col2) -- col2 unchanged if condition2 false    ,etc.
WHERE same_select_conditions

HTH --
Chrysalis

"FABRICATE DIEM PVNC"
(To to protect and to serve)
Motto of the City Guard
Terry Pratchett Received on Fri Apr 24 1998 - 15:37:48 CDT