Delete duplicate rows from Table in SQL

Below example shows how to delete duplicate records from Table using DELETE statement in SQL:

For Example:

1) Create table: XX_EMP

CREATE TABLE xx_emp (employee_id NUMBER, first_name VARCHAR2(60), salary NUMBER);

2) Insert records in XX_EMP table:

    i)  INSERT INTO xx_emp (employee_id, first_name, salary)

        VALUES (101, 'ABC', 20000);

   ii)  INSERT INTO xx_emp (employee_id, first_name, salary)

        VALUES (101, 'ABC', 20000);

   iii) INSERT INTO xx_emp (employee_id, first_name, salary)

        VALUES (102, 'XYZ', 20000);

  COMMIT;

3) Use SELECT statement to display inserted records:

    SELECT * FROM xx_emp;

    Output:

     --------------------------------------------------------------

     EMPLOYEE_ID         FIRST_NAME          SALARY

     --------------------------------------------------------------

     101                           ABC                       20000

     101                           ABC                       20000

     102                           XYZ                        20000

     --------------------------------------------------------------

4) Use DELETE script to delete Duplicate records from Table: XX_EMP:

    DELETE FROM xx_emp

    WHERE rowid NOT IN ( SELECT max(rowid) 
                                        FROM xx_emp
                                        GROUP BY employee_id, first_name, salary
                                      );

    COMMIT;

5) Use SELECT statement to check – duplicate records deleted or Not from XX_EMP table:

    SELECT * FROM xx_emp;

    Output:

     --------------------------------------------------------------

     EMPLOYEE_ID         FIRST_NAME          SALARY

     --------------------------------------------------------------

     101                           ABC                       20000

     102                            XYZ                        20000

     --------------------------------------------------------------