-
Junior Member
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;
-
Expert Member
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;
-
Junior Member
Re: Updating million's of record.
I did the same as you suggested .Thanks for reply.
-
Expert Member
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.
-
Contributing Member
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
-
Forum Rules