Can we update multiple record with join of two query

oracle

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 ???

Best Answer

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