SQL> desc location
Name Null? Type
----------------------------------------- -------- -----------------
LOCATION_ID NUMBER(5)
REL_DIRECTORY VARCHAR2(100)

location table has 1200o rows
SQL> desc TEST
Name Null? Type
----------------------------------------- -------- ------------------
BATCH_NAME NUMBER(10)
REL_DIRECTORY VARCHAR2(100)
FILENAME VARCHAR2(50)
REL_DATE DATE
ACC_NUMBER VARCHAR2(26)
DOC_TYPE VARCHAR2(5)
CUST_NAME VARCHAR2(30)
VIN VARCHAR2(20)
C_CODE VARCHAR2(6)
BRANCH_CODE VARCHAR2(8)
IMAGE_TYPE VARCHAR2(2)
ZIP_DATE DATE
SENT_DATE DATE
LOCATION_ID NUMBER(5)

table test has 26 million rows...

update TEST a set a.location_id=(select b.location_id FROM location b WHERE a.rel_directory=b.rel_directory)

this above update works in my development database ...without this procedure ...




create or replace PROCEDURE update_testing is
cursor c1 is select a.EMP_ID val_id from LOCATION A ,TEST B WHERE a.REL_DIRECTORY=b.REL_DIRECTORY;
my_counter number(12);
begin
my_counter:=0;
for c2 in c1 loop
UPDATE TEST SET EMP_ID=C2.VAL_ID;
my_counter:=my_counter+1;
if my_counter=50000 then
my_counter:=0 ;
end if;
COMMIT;
end loop;
end;

This procedure is not working and the results are not correct...
pls correct this procedure

The cursor and the update should commit every 50000 rows .

This i want as urgent as possible