DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: tune UPDATE

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598

    Question tune UPDATE

    UPDATE TMP_PERF_CRLIMIT a
    SET a.occ_com_map_code = (SELECT b.occ_com_map_code
    FROM MV_REF_OCC_COM_MAP b
    WHERE a.process_seq_no = v_seq_no
    AND a.emplm_type_code = b.occ_code
    AND a.se_emplm_code = b.company_type_code
    AND a.emplm_type_code = ICconstant.EMPL_TYPE_SELFEMPLOYED)
    WHERE EXISTS(
    SELECT 'X'
    FROM MV_REF_OCC_COM_MAP b
    WHERE a.process_seq_no = v_seq_no
    AND a.emplm_type_code = b.occ_code
    AND a.se_emplm_code = b.company_type_code
    AND a.emplm_type_code = ICconstant.EMPL_TYPE_SELFEMPLOYED) ;
    Cheers!
    OraKid.

  2. #2
    Join Date
    Feb 2001
    Posts
    180
    Maybe this will help:
    First select everything you wanna know
    Save 1 extra select.
    and then update by rowid

    -- Build a cursor
    declare curcor c_1
    is
    SELECT a.rowid row_id
    , b.occ_code
    , ....
    FROM MV_REF_OCC_COM_MAP b
    , TMP_PERF_CRLIMIT a
    WHERE a.process_seq_no = v_seq_no
    AND a.emplm_type_code = b.occ_code
    AND a.se_emplm_code = b.company_type_code
    AND a.emplm_type_code = ICconstant.EMPL_TYPE_SELFEMPLOYED
    ;
    begin
    for vc_1 in c_1
    loop
    update MV_REF_OCC_COM_MAP
    set occ_com_map_code = vc_1.occ_code
    where rowid = vc_1.row_id
    ;
    -- commit once and a while
    end loop;
    end;
    /


    Other way sometimes is to store the codes in a pl/sql-table
    but then you need a reference to the index.
    Regards
    Ben de Boer

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    thnx bensr
    Cheers!
    OraKid.

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