Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Update large set of rows with batch numbers

Posted on 2014-01-15
3
Medium Priority
?
1,569 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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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 …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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.

618 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