3 Ways to Delete Duplicate Record From table in Oracle

June 9, 2020Posted inORACLE

How to delete duplicate record From table using SQL/Oracle?

  • Create table using following script:
Copy Code
CREATE TABLE TEST_EMP
(
  ENAME   VARCHAR2(10 BYTE),
  DEPTNO  NUMBER(2),
  ID      NUMBER
)

  • Run following script for demo purpose.
Copy Code
SET DEFINE OFF;
Insert into TEST_EMP (ENAME, DEPTNO, ID)
 Values ('SMITH', 20, 1);
Insert into TEST_EMP (ENAME, DEPTNO, ID)
 Values ('ALLEN', 30, 2);
Insert into TEST_EMP (ENAME, DEPTNO, ID)
 Values ('SMITH', 20, 3);
Insert into TEST_EMP (ENAME, DEPTNO, ID)
 Values   ('VIKAS', 10, 4);
Insert into TEST_EMP   (ENAME, DEPTNO, ID)
 Values   ('VIKAS', 10, 6);
Insert into TEST_EMP   (ENAME, DEPTNO, ID)
 Values   ('ASHISH', 40, 7);
Insert into TEST_EMP   (ENAME, DEPTNO, ID)
 Values   ('SATISH', 50, 8);
Insert into TEST_EMP   (ENAME, DEPTNO, ID)
 Values   ('SATISH', 50, 9);
COMMIT;

  • First check how many duplicate record in the table using this query.
Copy Code
SELECT * FROM test_emp
WHERE ID NOT IN 
(SELECT MIN (ID)  FROM test_emp GROUP BY ename);

Or

SELECT * FROM test_emp
WHERE ID NOT IN 
(SELECT max (ID)  FROM test_emp GROUP BY ename);


Solution 1 :

Copy Code
DELETE FROM test_emp
      WHERE id NOT IN (  SELECT MIN (id)
                           FROM test_emp
                       GROUP BY ename, deptno);

OR

DELETE FROM test_emp
      WHERE id NOT IN (  SELECT MAX (id)
                           FROM test_emp
                       GROUP BY ename, deptno);



Solution 2 :

Copy Code
DELETE FROM test_emp a
      WHERE ID > (SELECT MIN (ID)
                  FROM test_emp b
                  WHERE a.deptno = b.deptno AND a.ename = b.ename);


Solution 3 :

Copy Code
DELETE FROM test_emp
      WHERE ID IN (
       SELECT ID FROM 
       			(SELECT ID,
                        ROW_NUMBER () OVER (PARTITION BY ename, deptno ORDER BY ID) r
                     FROM test_emp)
               WHERE r > 1);


You might also like

Tags: