Merge condition not working in oracle when trying to merge

Question

I have a table as:

--------------
|id   descrip |
|1  A         |
|2  B         |
|3  C         |
|3  D         |
|3  E         |
|4  F         |
|4  F         |

My expected output is:

--------------
|id descrip |
|1  A       |
|2  B       |
|3C C       |
|3D D       |
|3E E       |
|4  F       |
|4  F       |

I tried using first method:

merge into rules_table a
using rules_table b
on (a.id = b.id and a.descrip and a.rowid > b.rowid)
when matched then
  update set a.id = b.id || b.descrip

The second method i tried is:

update rules_table a
   set a.id =
       (select b.id || b.descrip
          from rules_table b
         where b.id = a.id
           and b.descrip = a.descrip
           and a.rowid = b.rowid)

But,I am not getting desired output.

Ofcourse the result of the two statement will not be same. In `Merge` you havent specified the action when record is not matched — XING, Nov 06 '19 at 08:43
`a.id=b.id and a.descrip` this will always fail..!! Correct it. — XING, Nov 06 '19 at 09:35

Radagast81 · Accepted Answer · 2019-11-06T09:15:53.900

2

Based on your expected result I think you are looking for the following:

MERGE INTO rules_table a
USING (SELECT rowid, id, descrip, COUNT(DISTINCT descrip) over (PARTITION BY id) cnt
         FROM rules_table) b
 ON (a.rowid = b.rowid AND b.cnt > 1)
 WHEN MATCHED THEN UPDATE 
  SET a.id=b.id||b.descrip

Also the following should work:

UPDATE rules_table
   SET id = id||descrip
 WHERE id IN (SELECT id
                FROM rules_table
               GROUP BY id
              HAVING COUNT(DISTINCT descrip) > 1)

edited Nov 06 '19 at 09:15

answered Nov 06 '19 at 08:52

Radagast81

2,921
1
7
21

COLUMN REFRENCED IN THE ON CLAUES CANNOT BE UPDATED: "a"."ID" – AB21 Nov 06 '19 at 09:03
I think as the same table is used you can use rowid instead, changed the answer accordingly. – Radagast81 Nov 06 '19 at 09:07

Merge condition not working in oracle when trying to merge

1 Answers1