Results 1 to 5 of 5

Thread: Updating million's of record.

  1. #1
    Junior Member
    Join Date
    May 2008
    Answers
    9

    Updating million's of record.

    I have to update 500000 records at a time and commit it.and then next 500000 records .There is one to many relationship between line-line_esn table.How could i save the last updated record so that i can update next 500000 records after that.Here is Pl/SQL script.Thanks



    declare
    cursor mdn_cursor is
    select le.line_id,le.line_esn_id,le.mdn, substr(le.mdn,7,10)
    from line_esn le
    where le.line_esn_id in (select max(le.line_esn_id)
    from line_esn le, line l
    where le.line_id = l.line_id
    group by le.line_id)
    and row_num <= 500000;
    begin
    for mdn_rec in mdn_cursor
    loop
    EXIT WHEN mdn_cursor%NOTFOUND;
    update line set account_passcode=mdn_rec.passcode
    where line_id=mdn_rec.line_id;
    end loop;
    end;


  2. #2
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Updating million's of record.

    Using ROWNUM is not correct procedure here.
    Simply do the following modifications in your code.

    declare
    count number := 0;
    cursor mdn_cursor is
    select le.line_id,le.line_esn_id,le.mdn, substr(le.mdn,7,10)
    from line_esn le
    where le.line_esn_id in (select max(le.line_esn_id)
    from line_esn le, line l
    where le.line_id = l.line_id
    group by le.line_id);
    --and row_num <= 500000;(DELETE THIS LINE)
    begin
    for mdn_rec in mdn_cursor
    loop
    EXIT WHEN mdn_cursor%NOTFOUND;
    update line set account_passcode=mdn_rec.passcode
    where line_id = mdn_rec.line_id;
    count := count +1;
    IF mod (count % 500000) = 0 then
    COMMIT;
    END IF:

    end loop;
    end;


  3. #3
    Junior Member
    Join Date
    May 2008
    Answers
    9

    Re: Updating million's of record.

    I did the same as you suggested .Thanks for reply.


  4. #4
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Updating million's of record.

    You can do it using rownum also. But for that you need to maintain a flag for updated records in your table.


  5. #5
    Contributing Member
    Join Date
    Apr 2006
    Answers
    46

    Re: Updating million's of record.

    why not using bulk update method?


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact