需要将此mysql更新脚本转换为oracle脚本

Question

我有一个查询在MySQL中运行良好，但当我在Oracle上运行它时，我会得到以下错误：

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

查询是：

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

这有帮助吗？

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