tune UPDATE

[balajiyes]

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) ;

[bensr]

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.

[balajiyes]

thnx bensr