BirgittaHauser · March 26, 2024 00:58 · GlennGundermann · Oct 17, 2022 · BirgittaHauser · Oct 17, 2022
diff --git a/Delete Duplicate Rows b/Delete Duplicate Rows
 -- Question in an Forum: I have a table/file which has duplicate records (Lets say there are 10 fields in a record). 
 --                       Out of which, if 5 fields put together are same across more than 1 record, then it is considered as a duplicate). 
 --                       I need to delete those duplicate records alone using a SQL statement embedded in a SQLRPGLE program.

 -- 1. Solution with a Cursor in embedded SQL (using a Common Table Expression for determining the duplicate rows!)
 **Free
 //---------------------------------------------------------------------------------------------
  CTl-Opt DatFmt(*ISO)  Option(*NoDebugIO);
 //---------------------------------------------------------------------------------------------
  DCL-S  GblRRN   Int(20);
 //---------------------------------------------------------------------------------------------
  Exec SQL Set Option Commit=*NONE, DatFmt=*ISO; 
 //---------------------------------------------------------------------------------------------
  *INLR = *On;

  Monitor;
     Exec SQL 
          Declare CsrDupRow Cursor For
              With x as (Select Key1, Key2, Key3, Key4, Key5, Max(RRN(a)) MaxRRN
                           From MyTable a
                                Group By Key1, Key2, Key3, Key4, Key5
                                Having Count(*) > 1)
              Select RRN(b)  
                from x join MyTable b on     x.Key1 = b.Key1 
                                         and x.Key2 = b.Key2 
                                         and x.Key3 = b.Key3
                                         and x.Key4 = b.Key4
                                         and x.Key5 = b.Key5
                                         and rrn(b) <> MaxRRN;

      Exec SQL Open CsrDupRow;
      If SQLCODE < *Zeros;
         //Handle Error
         Return;
      EndIf;

      DoU 1=0;
          Exec SQL  Fetch Next from CsrDupRow into :GblRRN;
          If SQLCODE < *Zeros;
             //Handle Error
             Leave;
          ElseIf SQLCODE = 100;
             Leave;
          EndIf;

          Exec SQL Delete from MyTable b Where RRN(b) = :GblRRN;
          If SQLCODE < *Zeros;
             //Handle Error
          EndIf;
      EndDo;

      Exec SQL Close CsrDupRow;

  On-Error;
    //Handle Error
  EndMon;

  Return;        
  
  -- 2. Solution: Handle everything in a DELETE Statement
  -- Unfortunately Common Table Expressions cannot be used within a DELETE Statement, but nested sub-Selects can:
  
  Delete from MyTable c
  Where rrn(c) in (Select RRN(b) rrnDlt
                     from (Select  Key1, Key2, Key3, Key4, Key5, 
                                   Max(RRN(a)) MaxRRN
                             from MyTable a
                             Group By Key1, Key2, Key3, Key4, Key5) x
                          join MyTable b on     x.Key1 = b.Key1 
                                            and x.Key2 = b.Key2 
                                            and X.Key3 = b.Key3
                                            and x.Key4 = b.Key4
                                            and x.Key5 = b.Key5
                                            and RRN(b) <> MaxRRN);
No results found