Solved

Update large set of rows with batch numbers

Posted on 2014-01-15
3
1,316 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
3 Comments
 
LVL 34

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 73

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now