Sayantan's Blog On Oracle

How to Delete Duplicate Records in SQL – Oracle

Duplicate records indicate that the current record is exactly copy of another record.

Creating setup for duplicate records:

CREATE TABLE EMP_DUP
AS
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE 1 = 2; 

DESC EMP_DUP;

Removing not null constraint for allowing duplicate records to be inserted into EMP_DUP table.

ALTER TABLE EMP_DUP MODIFY(LAST_NAME NULL);
ALTER TABLE EMP_DUP MODIFY(JOB_ID NULL);

DESC EMP_DUP;

Inserting duplicate records into EMP_DUP table:

--How to Delete Duplicate Records in SQL - Oracle: Example

BEGIN
  FOR I IN 1..2
  LOOP
    INSERT INTO EMP_DUP
    SELECT EMPLOYEE_ID, 
           FIRST_NAME, 
           LAST_NAME, 
           JOB_ID, 
           SALARY, 
           DEPARTMENT_ID
    FROM EMPLOYEES
    WHERE DEPARTMENT_ID = 30;
  END LOOP;
END;
/
COMMIT;

SELECT * FROM EMP_DUP ORDER BY EMPLOYEE_ID;

How would you see duplicate records from a table?

--How to Delete Duplicate Records in SQL - Oracle: Example

SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       SALARY, 
       DEPARTMENT_ID, 
       COUNT(1) COUNT 
FROM EMP_DUP
GROUP BY EMPLOYEE_ID, 
         FIRST_NAME, 
         LAST_NAME, 
         JOB_ID, 
         SALARY, 
         DEPARTMENT_ID
HAVING COUNT(1) > 1;

Finding duplicate records via Analytical Function:

--How to Delete Duplicate Records in SQL - Oracle: Example: Through Analytical function

SELECT * 
FROM(SELECT EMPLOYEE_ID, 
            FIRST_NAME, 
            LAST_NAME, 
            JOB_ID, 
            SALARY, 
            DEPARTMENT_ID, 
            COUNT(1) OVER(PARTITION BY EMPLOYEE_ID ORDER BY EMPLOYEE_ID) COUNT 
     FROM EMP_DUP)
WHERE COUNT > 1;

Using WITH clause:

----How to Delete Duplicate Records in SQL - Oracle: Example: WITH Clause

WITH
DUP_DATA
AS
  (SELECT EMPLOYEE_ID, 
          FIRST_NAME, 
          LAST_NAME, 
          JOB_ID, 
          SALARY, 
          DEPARTMENT_ID, 
          COUNT(1) OVER(PARTITION BY EMPLOYEE_ID ORDER BY EMPLOYEE_ID) COUNT 
   FROM EMP_DUP)
SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       JOB_ID, 
       SALARY, 
       DEPARTMENT_ID,
       COUNT
FROM DUP_DATA
WHERE COUNT > 1;

How do you remove duplicate records in SQL?:

There are multiple ways to delete duplicated records.

Removing Duplicate records from EMP_DUP table keeping the initial version:

• If no of records are handful then we can manually delete the records.
• If no of records are high then we can use ROWID with MIN and MAX function depending on which records we want to keep in database
• If we want to keep the initial records then we can use the MAX function
• If we want to keep the last records then we can use MAX function

DELETE EMP_DUP A
WHERE A.ROWID < (SELECT MAX(B.ROWID)
                 FROM EMP_DUP B
                 WHERE B.EMPLOYEE_ID = A.EMPLOYEE_ID);

SELECT * FROM EMP_DUP ORDER BY EMPLOYEE_ID;

ROLLBACK;

Removing Duplicate records from EMP_DUP table keeping the latest version:

DELETE EMP_DUP A
WHERE A.ROWID > (SELECT MIN(B.ROWID)
                 FROM EMP_DUP B
                 WHERE B.EMPLOYEE_ID = A.EMPLOYEE_ID);

SELECT * FROM EMP_DUP ORDER BY EMPLOYEE_ID;

ROLLBACK;

Correlated Delete:

If you want to delete the duplicate rows based on a list of column group then you can use the Corelated Delete.

Change in existing setup for Corelated Delete:

--https://asktom.oracle.com/pls/apex/f?p=100:11:::::P11_QUESTION_ID:1224636375004

ALTER TABLE EMP_DUP ADD(BUSINESS_DATE DATE);

BEGIN
  FOR I IN (SELECT ROWID, A.* 
            FROM EMP_DUP A
            ORDER BY ROWID 
            FETCH FIRST 6 ROWS ONLY) 
  LOOP
    UPDATE EMP_DUP
    SET BUSINESS_DATE = SYSDATE
    WHERE ROWID = I.ROWID;
  END LOOP;
  --
  FOR I IN (SELECT ROWID, A.* 
            FROM EMP_DUP A
            ORDER BY ROWID 
            OFFSET 6 ROWS FETCH NEXT 6 ROWS ONLY) 
  LOOP
    UPDATE EMP_DUP
    SET BUSINESS_DATE = SYSDATE - 1
    WHERE ROWID = I.ROWID;
  END LOOP;  
END;
/
COMMIT;

SELECT * FROM EMP_DUP ORDER BY BUSINESS_DATE;

DELETE EMP_DUP A
WHERE A.BUSINESS_DATE NOT IN (SELECT MIN(B.BUSINESS_DATE)
                              FROM EMP_DUP B
                              WHERE B.EMPLOYEE_ID = A.EMPLOYEE_ID
                              AND B.LAST_NAME = A.LAST_NAME
                              AND B.JOB_ID = A.JOB_ID);

Un-Correlated Delete:

If you want to delete records based on unique column then we should use Un-Correlated Delete.

INSERT INTO EMP_DUP(EMPLOYEE_ID, 
                    BUSINESS_DATE)
SELECT EMPLOYEE_ID, 
       SYSDATE + 1
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30;

SELECT * FROM EMP_DUP ORDER BY EMPLOYEE_ID;

DELETE EMP_DUP
WHERE BUSINESS_DATE NOT IN (SELECT MIN(BUSINESS_DATE)
                            FROM EMP_DUP
                            GROUP BY EMPLOYEE_ID);

SELECT * FROM EMP_DUP ORDER BY EMPLOYEE_ID;

RELATED TOPICS:

• Update multiple rows in Oracle using Join