0

Can we run Update query to update multiple records with join ? Below query is working fine when we running with one employee number -

update java set JAVA_MECHANISM='RECONCILIATION' 
where usr_key =(select usr_key from usr 
where usr_emp_no='******' and usr_status='Active') 
and JAVA_MECHANISM= 'ACCESS';

Now requirement is want to run this query with all users who exist with where filter of 2 tables where usr.usr_status='Active' and java.JAVA_MECHANISM= 'ACCESS'; i.e.

update oiu set OIU_PROV_MECHANISM='RECONCILIATION',
POL_KEY=null, OIU_POLICY_BASED=null,
OIU_POLICY_REVOKE=null 
where usr_key IN(select usr_key from usr where usr_status='Active' 
and usr_udf_isexecutiveuser='1') 
and OIU_PROV_MECHANISM = 'AP HARVESTED';

it’s taking too much time more than 10 mins but not completed as IN takes much time so any idea how we can get with oracle query ???

  • How many rows are being affected? Can you post the Plan, please? (Both update and merge) Does the cardinality of the Plan match expected? – Michael Kutz Jul 27 '19 at 23:10
0

Try merge instead, e.g.

merge into oiu o
  using (select u.usr_key
         from user u
         where u.usr_status = 'Active'
           and u.usr_udf_isexecutiveuser = '1'
        ) x
  on (o.usr_key = x.usr_key)
  when matched then update set
    o.oiu_prov_mechanism = 'RECONCILIATION',
    o.pol.key = null,
    o.oiu_policy_based = null,
    o.oiu_policy_revoke = null
  where o.oiu_prov_mechanism = 'AP HARVESTED';
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.