Remove duplicate records using dynamic SQL

Learn how to remove duplicate records present in a table with standard sql queries and also dynamic sql with example queries.

For base table reference please click on the given link.

To implement removing duplicate rows using dynamic SQL we just want to have the knowledge about Listagg, Execute Immediate concepts.

Below are example queries to remove the duplicate records.

--- Using max row id

DELETE 
FROM DUP_VALUES_TBL
WHERE ROWID 
IN (
    SELECT MAX(ROWID)
    FROM DUP_VALUES_TBL
    GROUP BY DUP_ID, DUP_NAME
    HAVING COUNT(DUP_ID) > 1 AND COUNT(DUP_NAME) > 1
   );

------ Using min row id  
DELETE 
FROM DUP_VALUES_TBL
WHERE ROWID 
IN (
    SELECT MIN(ROWID)
    FROM DUP_VALUES_TBL
    GROUP BY DUP_ID, DUP_NAME
    HAVING COUNT(DUP_ID) > 1 AND COUNT(DUP_NAME) > 1
   );   
   
-- using co-related query with min rowid

DELETE 
FROM DUP_VALUES_TBL A
WHERE ROWID >
 (
    SELECT MIN(ROWID)
    FROM DUP_VALUES_TBL B
    WHERE B.DUP_ID=A.DUP_ID
    AND   B.DUP_NAME=A.DUP_NAME   
   ); 

-- using co-related query with max rowid

DELETE 
FROM DUP_VALUES_TBL A
WHERE ROWID <
 (
    SELECT MAX(ROWID)
    FROM DUP_VALUES_TBL B
    WHERE B.DUP_ID=A.DUP_ID
    AND   B.DUP_NAME=A.DUP_NAME   
   );

--using Rownum wiht min rowid

DELETE FROM DUP_VALUES_TBL
WHERE ROWID NOT IN (
                     
                     SELECT MIN(rowid)                     
                     FROM DUP_VALUES_TBL
                     WHERE rownum <= (select count(1) from DUP_VALUES_TBL)
                      GROUP BY  DUP_ID, DUP_NAME
                    );
       
--using Rownum wiht Max rowid    
  
DELETE FROM DUP_VALUES_TBL
WHERE ROWID NOT IN (
                     
                     SELECT MAX(rowid)                     
                     FROM DUP_VALUES_TBL
                     WHERE rownum <= (select count(1) from DUP_VALUES_TBL)
                      GROUP BY  DUP_ID, DUP_NAME
                    );      
   
-- using row_number function   
DELETE 
FROM DUP_VALUES_TBL
WHERE rowid IN (SELECT rowid
                FROM (SELECT rowid rwid,
                             DUP_ID,
                             DUP_NAME,
                             row_number() over(partition by DUP_ID, DUP_NAME order by DUP_ID) rn
                      FROM DUP_VALUES_TBL
                      )
                  WHERE rn > 1
                );

--- using rank function with max rowid
DELETE 
FROM DUP_VALUES_TBL
WHERE rowid IN (SELECT MAX(rowid)
                FROM (SELECT rowid rwid,
                             DUP_ID,
                             DUP_NAME,
                             RANK() over(partition by DUP_ID, DUP_NAME order by DUP_ID) rn
                      FROM DUP_VALUES_TBL
                      )
                   GROUP by DUP_ID, DUP_NAME HAVING COUNT( rn) > 1
                );


--- using rank function with min rowid

DELETE 
FROM DUP_VALUES_TBL
WHERE rowid IN (SELECT MIN(rowid)
                FROM (SELECT rowid rwid,
                             DUP_ID,
                             DUP_NAME,
                             RANK() over(partition by DUP_ID, DUP_NAME order by DUP_ID) rn
                      FROM DUP_VALUES_TBL
                      )
                   GROUP by DUP_ID, DUP_NAME HAVING COUNT( rn) > 1
                );  


-- using dense rank function with max rowid
DELETE 
FROM DUP_VALUES_TBL
WHERE rowid IN (SELECT MAX(rowid)
                FROM (SELECT rowid rwid,
                             DUP_ID,
                             DUP_NAME,
                             dense_RANK() over(partition by DUP_ID, DUP_NAME order by DUP_ID) rn
                      FROM DUP_VALUES_TBL
                      )
                   GROUP by DUP_ID, DUP_NAME HAVING COUNT( rn) > 1
                );


-- using dense rank function with min rowid
DELETE 
FROM DUP_VALUES_TBL
WHERE rowid IN (SELECT MAX(rowid)
                FROM (SELECT rowid rwid,
                             DUP_ID,
                             DUP_NAME,
                             dense_RANK() over(partition by DUP_ID, DUP_NAME order by DUP_ID) rn
                      FROM DUP_VALUES_TBL
                      )
                   GROUP by DUP_ID, DUP_NAME HAVING COUNT( rn) > 1
                );

Query to remove duplicate records using dynamic SQL.

DECLARE
STRSQL VARCHAR2(1000);
V_COLS  VARCHAR2(1000);


BEGIN
        STRSQL :='SELECT DISTINCT (SELECT LISTAGG(COLUMN_NAME, '','') WITHIN GROUP(ORDER BY 1)
                                   FROM (SELECT COLUMN_NAME
                                         FROM USER_TAB_COLS
                                         WHERE TABLE_NAME = ''DUP_VALUES_TBL1''
                                         )
                                  )
                  FROM DUP_VALUES_TBL1';

EXECUTE IMMEDIATE STRSQL INTO V_COLS;

        STRSQL :='DELETE 
                  FROM DUP_VALUES_TBL1 
                  WHERE ROWID IN (  
                                  SELECT MAX(ROWID)
                                  FROM DUP_VALUES_TBL1 
                                  GROUP BY '|| V_COLS||' 
                                  HAVING COUNT(1) > 1)';
EXECUTE IMMEDIATE STRSQL;

END;

The above code is the declare block that can be compiled in run time environment.