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