Update and commit
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Update and commit

  1. #1
    Join Date
    Feb 2001
    Posts
    119
    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
    Share on Google+

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!

    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width