Problem in merge condition in oracle when merging two tables

Question

I have a table which has data as:

id  payor_name
---------------
1   AETNA
2   UMR
3   CIGNA
4   METLIFE
4   AETNAU
5   ktm
6   ktm

Id and payor_name are two columns.So,

My expected output is:

   id  payor_name
    ---------------
    1   AETNA
    2   UMR
    3   CIGNA
    4   METLIFE
    4   AETNAU
    6   ktm  ...> I want to change the id of this row to be 6 from 5.
    6   ktm

I want one to one mapping between id and payor_name.So,this is what I tried:

MERGE INTO offc.payor_collec A
USING (select id from offc.payor_collec where payor_name in( 
select payor_name from offc.payor_collec group by payor_name having count(distinct id)>=2)) B
ON (A.id=B.id)
WHEN MATCHED THEN
UPDATE SET A.id=B.id

But when I compiled I got error as:

Error at line 1
ORA-38104: Columns referenced in the ON Clause cannot be updated: "A"."ID"

Id is number where as payor_name is varchar2.

How can I achieve this result?

Answer 1

MERGE works, but slightly different than your code.

SQL> select * from test;

        ID PAYOR
---------- -----
         1 aetna
         2 umr
         5 ktm
         6 ktm

SQL> merge into test t
  2    using (select max(t1.id) id,
  3                  t1.payor_name
  4           from test t1
  5           group by t1.payor_name
  6          ) x
  7    on (x.payor_name = t.payor_name)
  8  when matched then update set
  9    t.id = x.id;

4 rows merged.

SQL> select * from test;

        ID PAYOR
---------- -----
         1 aetna
         2 umr
         6 ktm
         6 ktm

SQL>