Andrew Fraser DBA

Oracle remove duplicates parent child with listagg

De-duplicating parent – child table sets of data here using the listagg function to merge all the child rows into a single denormalised column.

set serverout on
BEGIN
FOR d1 IN (
     WITH denorm AS (
          SELECT parent.aos_code
               , parent.pre_group_id
               , LISTAGG ( child.aos_code || child.stud_pass ) WITHIN GROUP ( ORDER BY child.aos_code || child.stud_pass ) AS entry_value
            FROM StcAPreList parent
            JOIN StcAPreGroupAos child
              ON parent.pre_group_id = child.pre_group_id
           GROUP BY parent.aos_code , parent.pre_group_id
          )
   SELECT a.pre_group_id
     FROM denorm a
    WHERE a.pre_group_id > (
          SELECT MIN ( b.pre_group_id )
            FROM denorm b
           WHERE a.aos_code = b.aos_code
             AND a.entry_value = b.entry_value )
    ORDER BY 1
   )
LOOP
   dbms_output.put_line ( 'deleting ' || d1.pre_group_id ) ;
   DELETE FROM StcAPreGroupAos child WHERE child.pre_group_id = d1.pre_group_id ;
   DELETE FROM StcAPreList parent WHERE parent.pre_group_id = d1.pre_group_id ;
END LOOP ;
END ;
/