update about 100K records

From: Maryann Atkinson <maryann_30_at_yahoo.com>
Date: Tue, 08 Jul 2003 15:15:54 -0700
Message-ID: <F001.005C3E80.20030708150925@fatcity.com>

I have a table of about one million records.

About 100,000 of them have a flag which I need it set to 0.

Because of the size of the rollback segment, I cannot update them all and then commit, I need to do it in sets of 1000 records.
Do I need a cursor for this?

I had something like that:
DECLARE
     I BINARY_INTEGER;
Cursor MyCursor IS

        SELECT   *
        FROM     (Table_Name)
        WHERE    (Condition)
        FOR UPDATE;
BEGIN
     I := 0;
     FOR MyRec IN MyCursor LOOP
           UPDATE (Table_name)
          SET   delete_flag = 1
          WHERE         CURRENT OF MyCursor;
           I := I + 1;
           IF MOD(I, 1000) = 0 THEN
               COMMIT;
           END IF;

    END LOOP;
END;
/
But "FOR UPDATE" does not really work well, and at the 1000th record when it reaches the commit, its dropping out of the loop. Can I use ROWNUM to update them in batches of 1000 per time?

Thanks,
maa
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Maryann Atkinson
 INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Jul 08 2003 - 17:15:54 CDT