Solved

Update large set of rows with batch numbers

Posted on 2014-01-15
3
1,418 Views
Last Modified: 2014-01-15
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
0
Comment
Question by:klpayton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 39782734
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39782742
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
 

Author Comment

by:klpayton
ID: 39782767
Let me try these and I will reward whichever one works the fastest..
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question