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&#37;NOTFOUND;
update line set account_passcode=mdn_rec.passcode
where line_id=mdn_rec.line_id;
end loop;
end;

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

declare
[B]count number := 0; [/B]
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);
[B]--and row_num <= 500000;(DELETE THIS LINE)[/B]
begin
for mdn_rec in mdn_cursor
loop
EXIT WHEN mdn_cursor&#37;NOTFOUND;
update line set account_passcode=mdn_rec.passcode
where line_id = mdn_rec.line_id;
[B]count := count +1;
IF mod (count % 500000) = 0 then
COMMIT;
END IF:[/B]
end loop;
end;

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

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

why not using bulk update method?