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.

Answer 1

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)