Update large set of rows with batch numbers

I have a large table with over 2 million rows and I want to update all the rows in batches of 10000 with a certain value. Right now the batch nums are all set to 1000, but I want them to be for the first 10000 = 1000, second 10000=1001, third 10000=1002 and so on until the whole table is updated. I'm not really good with pl/sql but I think that will be the easiest way through some sort of loop. Example data below:


batch_num   emplid   name
1000             2223      Lisa Marie
1000            2224       Johnny Knoxville
1000           2233       Michael Jackson
klpaytonAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
no need for pl/sql or looping at all,  just one simple sql statement should do it


update yourtable set batch_num = 1000 + floor((rownum-1)/10000)
0
 
johnsoneSenior Oracle DBACommented:
This would be the quick and dirty way:

DECLARE 
    cnt       PLS_INTEGER := 0; 
    new_batch PLS_INTEGER := 1000; 
BEGIN 
    FOR c1rec IN (SELECT ROWID 
                  FROM   mytab) LOOP 
        UPDATE mytab 
        SET    batch_num = new_batch 
        WHERE  ROWID = c1rec.ROWID; 

        cnt := cnt + 1; 

        IF cnt = 10000 THEN 
          cnt := 0; 

          new_batch := new_batch + 1; 

          COMMIT; 
        END IF; 
    END LOOP; 

    COMMIT; 
END; 

/ 

Open in new window


You need to put in the correct table name for mytab.  There is a possibility that it would run into a fetch across commit situation, but it may not.  You would see that as an ORA-01555.  If it does, there are other ways around that.
0
 
klpaytonAuthor Commented:
Let me try these and I will reward whichever one works the fastest..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.