Delete Duplicate Records

2015.8.31

Query target

Deleteduplicate Emails

Writea SQL Query to delete all duplicate e-mail entries in a table named Person,keeping unique emails based on its SMA Llest Id.

Topics from Leetcode

The person table is as follows:

sql> SELECT * Fromperson;

ID EMAIL
------------------------------------------------------------
1 [email protected]
2 [email protected]
3 [email protected]

Build tables and insert test data

droptable person purge;

CreateTable person (id int,email char (50));

Insertinto person (Id,email) VALUES (1, ' [email protected] ');

Insertinto person (Id,email) VALUES (2, ' [email protected] ');

Insertinto person (Id,email) VALUES (3, ' [email protected] ');

The resulting person table has 3 rows of data. The third row of data should be deleted as required by the topic.

The SQL statements are as follows (performed in Oracle):

Method One: Use min

Sql>delete from Person A where a.id > (

Select min (b.id) from person B where a.email=b.email

);

Method Two: or with any

Sql>delete from Person A where a.id > any (

Select b.id from person bwhere a.email=b.email

);

Method Three: Use GROUP by & ROWID

Sql>deletefrom person where rowid not in (select min (rowid) from person Group by email);

Method Four: Self-join

Sql>delete from person P1 where rowid not in (select min (rowid) from person P2 where p1.email=p2.email);

Method Five: Use Row_number ()

SQL > Delete from person where rowid in (select Rid from (select rowID rids, Row_number () over (partition by email order by email) (Row_num from person) where row_num>1);

Method Six: Use Dense_rank ()

Delete from person where rowid in (select Rid from (select Rowidrid, Dense_rank () over (partition by e-mail order by ROWID) Rank Fromperson) where rank>1);

Reference article:

Http://www.dba-oracle.com/t_delete_duplicate_table_rows.htm

http://sqlandplsql.com/2013/01/29/5-ways-to-delete-duplicate-records-oracle/

The main contents are as follows:

In Oracle there is many ways to delete duplicate records. Notethat Below example is described to just explain the different possibilities.

Considerthe EMP table with below rows

CreateTable EMP (
Empnno Integer,
EmpName varchar2 (20),
SALARY number);

Bill 2000
Bill 2000
Mark 3000
Mark 3000
Mark 3000
Tom 4000
Tom 5000
Susan 5000

1. Usingrowid

SQL >delete from EMP
where rowID not in
(select Max (ROWID) from the EMP Group by empno);

Thistechnique can applied to almost scenarios. Group by operation should is onthe columns which identify the duplicates.

2. Usingself-join

sql> Delete from EMP E1
where rowID not in
(select Max (ROWID) from EMP E2
where e1.empno = E2.empno);

3. Using Row_number ()

sql> Delete from emp where rowid in
(
Select Rid from
(
Select rowID RIDs,
Row_number () over (partition by empno ORDER by Empno) RN
From EMP
)
where RN > 1
);

This isanother efficient-to delete duplicates

4. Usingdense_rank ()

sql> Delete from emp where rowid in
(
Select Rid from
(
Select rowID RIDs,
Dense_rank () over (partition by empno ORDER by ROWID) RN
From EMP
)
where RN > 1
);

Here Youcan use both rank () and Dens_rank () since both would give unique records whenorder by ROWID.

5. Using GroupBy

Considerthe EMP table with below rows

Bill 2000
Bill 2000
Mark 3000
Mark 3000

sql> Delete from EMP where
(Empno,empname,salary) in
(
Select Max (empno), empname,salary from EMP
GROUP BY Empname,salary
);

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Delete Duplicate Records