Loop through select query results from a table and Update column in same table

Question

Table A has the data like this

JID  P_ID P_CODE   COMM      MDATE
---  ---- ------   ----      ---------
1     112  WXCVA   null      2-feb-19  
1     112  UCXVA   WXCVA     09-sep-19 
2     222  DCVA    null      08-aug-18   
2     222  UCVA    DCVA      09-dec-09    

There are 200 records like this. Now I need to read those records and update the mdate for secondary codes. The result table need to look like this

JID  P_ID P_CODE   COMM      MDATE
---  ---- ------   ----      ---------
1     112  WXCVA   null      2-feb-19  
1     112  UCXVA   WXCVA     2-feb-19 
2     222  DCVA    null      08-aug-18   
2     222  UCVA    DCVA      08-aug-18    

How to achieve that?

Answer 1

create table mytab(p_id number, p_code varchar2(10), comm varchar2(10), mdate date);

insert into mytab values(112, 'WXCVA', null, to_date('20190202','yyyymmdd'));
insert into mytab values(112, 'UCXVA', 'WXCVA', to_date('20190909','yyyymmdd'));
insert into mytab values(222, 'DCVA', null, to_date('20180808','yyyymmdd'));
insert into mytab values(222, 'UCVA', 'DCVA', to_date('20091209','yyyymmdd'));

COMMIT;

select * from mytab;

P_ID P_CODE COMM  MDATE
112  WXCVA  NULL  02-FEB-19
112  UCXVA  WXCVA 09-SEP-19
222  DCVA   NULL  08-AUG-18
222  UCVA   DCVA  09-DEC-09

Merge statement to perform the required update.

MERGE INTO MYTAB C
USING (SELECT A.P_ID, B.P_CODE, B.COMM, A.MDATE 
         FROM MYTAB A 
              INNER JOIN 
              MYTAB B 
           ON A.P_ID = B.P_ID 
          AND A.P_CODE = B.COMM) D
   ON (   C.P_ID = D.P_ID 
      AND C.P_CODE = D.P_CODE)
 WHEN MATCHED 
 THEN UPDATE SET C.MDATE = D.MDATE;

select * from mytab;

P_ID P_CODE COMM  MDATE
112  WXCVA  NULL  02-FEB-19
112  UCXVA  WXCVA 02-FEB-19
222  DCVA   NULL  08-AUG-18
222  UCVA   DCVA  08-AUG-18