Primary discussion on Oracle delete duplicate records, keep only rowid minimum records

Title, Preliminary discussion on Oracle delete duplicate records, keep only rowid minimum records (ROWID can reflect the order in which data is inserted into the database)

There are several ways to delete duplicate records, and the following are just two ways of exist and in two.

1. First create a test table.

Create Table my_users (    number,    varchar2),    number )

2. Inserting test data

begin     forIinch  1..TenLoopInsert  intoMy_usersValuesI'Carl_zhang', I+Ten); EndLoop;End;begin     forIinch  1..TenLoopInsert  intoMy_usersValuesI'Carl_zhang', I+ -); EndLoop;End;Insert  intoMy_usersValues( -,'Carl',20.3);Commit;

3. View duplicate Records

SelectRowid,rownum,a.*  fromMy_users awhere 1=1 and exists(    Select ' exist '  fromMy_users bwhere 1=1     anda.ID=b.id andA.username=B.username having Count(*)>1    )Order  byrowID

4. Viewing duplicate data, rowid the largest record (rowID can reflect the order in which the data is inserted into the database)

SelectRowid,rownum,a.*  fromMy_users awhere 1=1 and exists(    Select ' exist '  fromMy_users bwhere 1=1     anda.ID=b.id andA.username=B.username--Having count (*) >1     having Count(*)>1  andA.rowid=Max(B.rowid))Order  byrowID

5. Delete duplicate data, keep ROWID minimum record

Delete   fromMy_users awhere 1=1 and exists(    Select ' exist '  fromMy_users bwhere 1=1     anda.ID=b.id andA.username=B.username--Having count (*) >1     having Count(*)>1  andA.rowid=Max(B.rowid))

The above method is realized by exist, which is faster than in and not.

1. Review the duplicate records as follows.

SelectRowid,rownum,a.*  fromMy_users awhere 1=1 and(A.id,a.username)inch(    SelectB.id,b.username fromMy_users bwhere 1=1       having Count(*)>1    Group  byb.id,b.username)Order  byrowID

2. Viewing duplicate data, rowid the largest record

SelectRowid,rownum,a.*  fromMy_users awhere 1=1 and(A.ID,A.USERNAME,ROWID)inch(    SelectB.id,b.username,Max(ROWID) fromMy_users bwhere 1=1       having Count(*)>1    Group  byb.id,b.username)Order  byrowID

3. Delete duplicate data, keep ROWID minimum record

Delete  fromMy_users awhere 1=1 and(A.ID,A.USERNAME,ROWID)inch(    SelectB.id,b.username,Max(ROWID) fromMy_users bwhere 1=1       having Count(*)>1    Group  byb.id,b.username)

Primary discussion on Oracle delete duplicate records, keep only rowid minimum records