Help with update statement

[OraMaster]

Hi

I have two tables along with staructure and sample data below.

Table 1 :

final_temp(club_id nvarchar(36), effective_date datetime,club_status nvarchar2(20), termination_reason nvarchar2(50))

club_id effective_date club_status termination_reason
-----------------------------------------------------------------------------------
1 01-Apr-2001 Active
1 01-Apr-1998 Terminated Payment Due
1 01-Jun-1982 Active
------------------------------------------------------------------------------------

Table 2:
client_ri_idhistory(club_id nvarchar2(36), seqnum int, effective_date datetime, newdata nvarchar2(50))

club_id seqnum effective_date newdata
------------------------------------------------------------------
1 13432 01-Apr-2001 Active
1 62378 01-Apr-1998 Terminated
1 83723 01-Apr-1998 Reactivated
-------------------------------------------------------------------

Now if there are more than one record with same effective_date for one club_id in client_ri_idhistory table with 'Terminated' and 'Reactivated' statuses then I need to update club_status = 'Active' in final_temp table for the same club_id and effective_date.

Please let me know if you need more information on the same.

Thanks!
Bhushan