Solved

Update large set of rows with batch numbers

Posted on 2014-01-15
3
1,396 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 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

766 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