How to Delete duplicate email_ids from table using SQL.

Sometimes when we upload any csv data or import any data may be a chance that our table have unique column id but having duplicate emails. In that case we have to delete duplicate emails.

SQL> CREATE TABLE EMPLOYEE(EMPNO INT PRIMARY KEY,
  2                        ENAME VARCHAR2(30) NOT NULL,
  3                        EMAIL_ID VARCHAR2(20));

Table created.

SQL> INSERT INTO EMPLOYEE VALUES(1001,'Parag C Shukla','pcs@gmail.com');

1 row created.

SQL> INSERT INTO EMPLOYEE VALUES(1002,'Kapil K Shukla','kks@gmail.com');

1 row created.

SQL> INSERT INTO EMPLOYEE VALUES(1003,'Patni Computer System','pcs@gmail.com');

1 row created.

SQL> INSERT INTO EMPLOYEE VALUES(1004,'Kalpataru Knowledge System','kks@gmail.com');

1 row created.

SQL> INSERT INTO EMPLOYEE VALUES(1005,'Deven J Patel','djp@gmail.com');

1 row created.

SQL> INSERT INTO EMPLOYEE VALUES(1006,'Hariom Tatsat','djp@gmail.com');

1 row created.

Following query help you to delete the duplicate email id from table.

DELETE
FROM EMPLOYEE
WHERE ROWID IN
   (SELECT ROWID FROM
        (SELECT EMPNO,ENAME,EMAIL_ID,RANK() OVER(PARTITION BY EMAIL_ID ORDER BY EMPNO)RANK
        FROM EMPLOYEE)T
     WHERE RANK>1 );