Delete Duplicate records, duplicaterecords

Delete Duplicate records, duplicaterecords

2015.8.31

Query target

DeleteDuplicate Emails

Writea SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.

Question from Leetcode

The Person table is as follows:

SQL> select * fromperson;

ID EMAIL
------------------------------------------------------------
1 jo@ex.com
2 jom@ex.com
3 jo@ex.com

 

Create tables and insert Test Data

Droptable person purge;

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

Insertinto person (id, email) values (1, 'jo @ ex.com ');

Insertinto person (id, email) values (2, 'jom @ ex.com ');

Insertinto person (id, email) values (3, 'jo @ ex.com ');

 

The resulting Person table contains three rows of data. The third row of data should be deleted as required.

The SQL statement is as follows (executed in oracle ):

Method 1: Use MIN

SQL> delete from person a where a. id> (

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

);

Method 2: or use ANY

SQL> delete from person a where a. id> any (

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

);

Method 3: Use group by & ROWID

SQL> deletefrom person where rowid not in (select min (rowid) from person group by email );

Method 4: self-join

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

Method 5: Use ROW_NUMBER ()

SQL> delete from person where rowid in (select rid from (select rowid rid, row_number () over (partition by email order by email) row_num from person) where row_num> 1 );

Method 6: Use DENSE_RANK ()

Delete from person where rowid in (select rid from (select rowidrid, dense_rank () over (partition by email order by rowid) rank fromperson) where rank> 1 );

 

References:

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 content is as follows:

In Oracle there are always ways to delete duplicate records. Notethat below example are described to just explain the different possibilities.

Considerthe EMP table with below rows

Createtable emp (
EMPNNO integer,
EMPNAME varchar2 (20 ),
SALARY number );

10 Bill 2000
11 Bill 2000
12 Mark 3000
12 Mark 3000
12 Mark 3000
13 Tom 4000
14 Tom 5000
15 Susan 5000

1. Usingrowid

SQL> delete from emp
Where rowid not in
(Select max (rowid) from emp group by empno );

Thistechnique can be applied to almost scenarios. Group by operation shoshould be 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 rid,
Row_number () over (partition by empno order by empno) rn
From emp
)
Where rn> 1
);

This isanother efficient way to delete duplicates

4. Usingdense_rank ()

SQL> delete from emp where rowid in
(
Select rid from
(
Select rowid rid,
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 will give unique records whenorder by rowid.

5. Using groupby

Considerthe EMP table with below rows

10 Bill 2000
11 Bill 2000
12 Mark 3000
13 Mark 3000

SQL> delete from emp where
(Empno, empname, salary) in
(
Select max (empno), empname, salary from emp
Group by empname, salary
);

 

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.