Need to convert this mysql update script to oracle script

Question

I have a query which works fine in MySQL, but when I run it on Oracle I get the following error:

SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"

The query is :

update branch_policy bp
  join (select  
          po.id as old_id,
          pn.new_id
        from policy po
          join (
            select
              policy_number,
              provider_id,
              min(id) as new_id
            from policy
            group by policy_number, provider_id) as pn
          on po.policy_number = pn.policy_number
            and po.provider_id = pn.provider_id) as p
  on bp.policy_id = p.old_id
set bp.policy_id = p.new_id
;

Answer 1

Would this help?

update branch_policy bp set
  bp.policy_id = (select pn.new_id
                  from (select policy_number, provider_id, nin(id) new_id
                        from policy
                        group by policy_number, provider_id
                       ) pn join policy po on po.policy_number = pn.policy_number
                                          and po.provider_id = pn.provider_id
                 )
where bp.policy_id in (select po.id
                       from (select policy_number, provider_id, nin(id) new_id
                             from policy
                             group by policy_number, provider_id
                            ) pn join policy po on po.policy_number = pn.policy_number
                                               and po.provider_id = pn.provider_id
                      );