Update and commit

[Subha]

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

[sambavan]

Subha,

I notice that this is a continuation on your previous post on the "Update and commit" issue. So, instead of keeping multiple threads to float around, I'm taking the liberty to close this thread and add a link to your previous thread of discussion. If you have any problem, please let us know.


Link to the previous discussion on the same issue http://www.dbasupport.com/forums/sho...threadid=12846


Thanx,
Sam