Gurus,
I am sorry I did not mock data, I just need thought or idea if this can be done. I am trying to update 5 tables based on same query that is in the exists clause as shown below. Each statement will update 1-2 million rows. Is there a better way of doing this instead of 5 different update statements? I tried PL/SQL by putting the repetitive query in the exists clause into a cursor and do bulk update (using cursor, bulk collect and forall) but it is not working as the indx cannot be applied to all tables (C_member_info_TAB(INDX).ID). Only the first table is getting updated and rest as updating as zero rows. I read online that One FORALL statement can support only one DML statement. Please let me know how to increase performance of this.
update member_info
set PII_data = null
where exists ( select 1
from member
inner join memeber_audit on (member.id = memeber_audit.id)
where member_info.id = member.id
and member.update_date >= (select create date
from config_table
where type = 'DEIDENTIFICATION') ;
update member_info_A
set PII_data = null
where exists ( select 1
from member
inner join memeber_audit on (member.id = memeber_audit.id)
where member_info_A.id = member.id
and member.update_date >= (select create date
from config_table
where type = 'DEIDENTIFICATION')
update member_info_B
set PII_data = null
where exists ( select 1
from member
inner join memeber_audit on (member.id = memeber_audit.id)
where member_info_B.id = member.id
and member.update_date >= (select create date
from config_table
where type = 'DEIDENTIFICATION')
update member_info_C
set PII_data = null
where exists ( select 1
from member
inner join memeber_audit on (member.id = memeber_audit.id)
where member_info_C.id = member.id
and member.update_date >= (select create date
from config_table
where type = 'DEIDENTIFICATION')
update member_info_D
set PII_data = null
where exists ( select 1
from member
inner join memeber_audit on (member.id = memeber_audit.id)
where member_infoD.id = member.id
and member.update_date >= (select create date
from config_table
where type = 'DEIDENTIFICATION')