sql - 需要将此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
;

标签： sqloraclejoinora-00933

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