-1

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
;
APC
  • 144,005
  • 19
  • 170
  • 281
jagadeesh reddy
  • 1
  • Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Stop trying to code your overall goal & explain what you expected instead from the given code & why. – philipxy Apr 21 '20 at 05:04
  • Before considering posting please read the manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. – philipxy Apr 21 '20 at 05:06
  • Oracle SQL does not support ANSI SQL 92 join syntax for DML statements. I agree it would be neat if it did. The linked question shows different ways to work around this limitation. – APC Apr 21 '20 at 06:00

1 Answers1

0

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
                      );
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Hi thanks for your quick reply, but this solution is not working because we can't update the columns those are using in the on clause ,**38104. 00000 "Columns referenced in the ON Clause cannot be updated: %s" ** – jagadeesh reddy Apr 21 '20 at 06:02
  • Ah, yes ... stupid me. Spent time trying to figure out what goes where, instead of *thinking* what I was doing. Sorry. I tried to rewrite it to UPDATE; have a look, please. – Littlefoot Apr 21 '20 at 06:11
  • Hi, this update query giving an error like ORA-01427: single-row subquery returns more than one row – jagadeesh reddy Apr 21 '20 at 07:16
  • It has something to do with data (duplicates) or join condition(s) in subquery that returns NEW_ID. A simple option might be to SELECT DISTINCT PN.NEW_ID. Otherwise, you'll have to check why it returned more than a single value. – Littlefoot Apr 21 '20 at 07:37
  • Hi @Littlefoot , before you have given one merge script right , that is working fine if we change script little bit in the end like this , ON (1=1) WHEN matched THEN UPDATE SET bp.id = p.new_id WHERE bp.id = p.old_id. thank you very much for your help – jagadeesh reddy Apr 21 '20 at 14:18