oracle Query Duplicate Data and Delete Duplicate Records Sample Sharing

1. Query a duplicate field

   select *
     from User u
     where u.user_name in (select u.user_name 
                 from User u
                group by u.user_name  having count(*) > 1)

2. Delete duplicates of some fields in the table

Example: There are 6 records in the table. Among them, the records of Zhang 3 and Wang 5 are duplicate
TableA

id customer PhoneNo 
001  Zhang 3 777777 
002  Li 4 444444 
003  Wang 5 555555 
004  Zhang 3 777777 
005  Zhang 3 777777 
006  Wang 5 555555 
 How to write 1 A sql Statement sets the TableA Become as follows  
001  Zhang 3 777777 
002  Li 4 444444 
003  Wang 5 555555

Test environment

create table TableA ( id varchar(3),customer varchar(5),PhoneNo varchar(6)) 
insert into TableA select '001',' Zhang 3','777777' 
union all select '002',' Li 4','444444' 
union all select '003',' Wang 5','555555' 
union all select '004',' Zhang 3','777777' 
union all select '005',' Zhang 3','777777' 
union all select '006',' Wang 5','555555'

Results

delete TableA from TableA Twhere

exists( 
select 1fromtablea where customer=T.customer and phoneno=T.phoneno 
andid < tt.id
)

Summarize

This method is suitable for having one field that is self-incremental, such as id in this example

delete  Table name  from  Table name  as Twhere

exists( 
select 1from Table name  where  Field A=T. Field A and  Field B=T. Field B,(....) 
and Self-addition  < T. Self-addition 
)

3. SQL statement for querying and deleting duplicate records

SQL statement for querying and deleting duplicate records

1. Lookup the redundant duplicate records in the table. The duplicate records are judged according to a single field (peopleId)

select * from people 
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) 

2. Delete redundant duplicate records in the table. The duplicate records are judged according to a single field (peopleId), and only the records with the smallest rowid are left

delete from people 
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) 
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

Note: rowid comes with oracle without this......

3. Redundant duplicate records (multiple fields) in the lookup table

select * from vitae a 
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) 

4. Delete redundant duplicate records (multiple fields) in the table, leaving only the records with the smallest rowid

delete from vitae a 
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) 
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) 

5. Redundant duplicate records (multiple fields) in the lookup table, excluding the records with the smallest rowid

select * from vitae a 
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) 
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1) 

(2)
For example
There is a field "name" in the A table,
And the "name" value may be the same between different records,
Now it is necessary to find out the items with duplicate values of "name" between the records in the table;

id customer PhoneNo 
001  Zhang 3 777777 
002  Li 4 444444 
003  Wang 5 555555 
004  Zhang 3 777777 
005  Zhang 3 777777 
006  Wang 5 555555 
 How to write 1 A sql Statement sets the TableA Become as follows  
001  Zhang 3 777777 
002  Li 4 444444 
003  Wang 5 555555

0

If the gender is also the same, it is as follows:

id customer PhoneNo 
001  Zhang 3 777777 
002  Li 4 444444 
003  Wang 5 555555 
004  Zhang 3 777777 
005  Zhang 3 777777 
006  Wang 5 555555 
 How to write 1 A sql Statement sets the TableA Become as follows  
001  Zhang 3 777777 
002  Li 4 444444 
003  Wang 5 555555

1

(3)

Method 1

id customer PhoneNo 
001  Zhang 3 777777 
002  Li 4 444444 
003  Wang 5 555555 
004  Zhang 3 777777 
005  Zhang 3 777777 
006  Wang 5 555555 
 How to write 1 A sql Statement sets the TableA Become as follows  
001  Zhang 3 777777 
002  Li 4 444444 
003  Wang 5 555555

2

set rowcount 0 Method 2
"Duplicate record" has two meanings of duplicate records, 1 is a completely duplicate record, that is, a record in which all fields are duplicate, and 2 is a record in which some key fields are duplicate, such as Name field, while other fields are not duplicate or duplicate can be ignored.
1. For the first repetition, it is easier to solve and use
select distinct * from tableName
You can get a result set without duplicate records.
If this table needs to delete duplicate records (keep 1 duplicate record), you can delete them as follows

select distinct * into #Tmp from tableName 
drop table tableName 
select * into tableName from #Tmp 
drop table #Tmp 

The reason for this duplication is due to poor table design, which can be solved by adding only 1 index column.

2. This kind of duplicate problem usually requires keeping the first record in duplicate records, and the operation method is as follows
Assuming that the duplicate fields are Name and Address, the result set of only 1 for these two fields is required

id customer PhoneNo 
001  Zhang 3 777777 
002  Li 4 444444 
003  Wang 5 555555 
004  Zhang 3 777777 
005  Zhang 3 777777 
006  Wang 5 555555 
 How to write 1 A sql Statement sets the TableA Become as follows  
001  Zhang 3 777777 
002  Li 4 444444 
003  Wang 5 555555

4

The last select gets the result set of Name and Address without duplication (but there is an extra autoID field, which can be written in the select clause when actually writing)

(4)
Query duplication

id customer PhoneNo 
001  Zhang 3 777777 
002  Li 4 444444 
003  Wang 5 555555 
004  Zhang 3 777777 
005  Zhang 3 777777 
006  Wang 5 555555 
 How to write 1 A sql Statement sets the TableA Become as follows  
001  Zhang 3 777777 
002  Li 4 444444 
003  Wang 5 555555

5