Three methods of deleting duplicate records in Oracle query

Time:2020-11-14

For example, there is a personnel table (Table Name: peosons)
If you want to record the name, ID number and address of the three fields exactly the same,

Copy codeThe code is as follows:
select p1.*  
from persons  p1,persons  p2  
where p1.id<>p2.id  
and  p1.cardid = p2.cardid and p1.pname = p2.pname and p1.address = p2.address

The above effect can be achieved

Several SQL statements for deleting duplicate records

1. Using ROWID method

2. Use group by method

3. Using distinct method

1。 Using ROWID method

According to the ROWID attribute of Oracle, judge whether there is repetition. The statement is as follows:

Check the data:

Copy codeThe code is as follows:
select * from table1 a where rowid !=(select max(rowid)
from table1 b where a.name1=b.name1 and a.name2=b.name2……)

Delete data:

Copy codeThe code is as follows:
delete  from table1 a where rowid !=(select max(rowid)
from table1 b where a.name1=b.name1 and a.name2=b.name2……)

2. Group by method

Check the data:

Copy codeThe code is as follows:
Select count (Num), max (name) from student — lists the number of duplicate records and lists his name attribute
group by num
Having count (Num) > 1 — after grouping by num, find out that the num column in the table is repeated, that is, the number of occurrences is greater than once

Delete data:

Copy codeThe code is as follows:
delete from student
group by num
having count(num) >1

In this way, all the duplicates are deleted.

3. Use distinct method – useful for small tables

Copy codeThe code is as follows:
create table table_new as   select distinct *   from table1 minux
truncate table table1;
insert into table1 select * from table_new;

Methods of querying and deleting duplicate records

1. Look up the redundant duplicate records in the table. The duplicate records are judged by a single field (peopleid)

Copy codeThe code is as follows:
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

2. Delete the redundant duplicate records in the table. The duplicate records are judged according to a single field (peopleid), leaving only the record with the smallest ROWID

Copy codeThe code is as follows:
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)

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

Copy codeThe code is as follows:
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 record with the smallest ROWID

Copy codeThe code is as follows:
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 record with the smallest ROWID

Copy codeThe code is as follows:
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 table a, and the value of “name” between different records may be the same. Now it is necessary to find out the items with duplicate “name” values among the records in the table;

Copy codeThe code is as follows:
Select Name,Count(*) From A Group By Name Having Count(*) > 1

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

Copy codeThe code is as follows:
Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1

(3)

Method 1

Copy codeThe code is as follows:
declare @max integer,@id integer
declare cur_ Rows cursor local for select main field, count (*) from table name group by main field having count (*) >; 1
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
Delete from table name where main field = @ ID
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0

Method 2

Duplicate record” has two meanings: one is completely duplicate record, that is, all fields are duplicated record, and the other is record with some key fields repeating,
For example, the name field is repeated, while the other fields are not necessarily repeated or are all repeated, which can be ignored.

1. For the first kind of repetition, it is easier to solve

Copy codeThe code is as follows:
select distinct * from tableName

You can get no duplicate records.

If the table needs to delete duplicate records (one duplicate record is reserved), you can delete them as follows

Copy codeThe code is as follows:
select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp

The reason for this kind of duplication is that the design of the table is not good. It can be solved by adding a unique index column.

2. This kind of duplication problem usually requires that the first record in the duplicate record be kept. The operation method is as follows

Assuming that there are duplicate fields of name and address, the unique result set of these two fields is required

Copy codeThe code is as follows:
select identity(int,1,1) as autoID, * into #Tmp from tableName
select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
select * from #Tmp where autoID in(select autoID from #tmp2)

The last select gets the result set without duplicate name and address (but there is an AutoID field, which can be omitted in the select clause when writing)

(4)

Duplicate query

Copy codeThe code is as follows:
select * from tablename where id in (
    select id from tablename
    group by id
    having count(id) > 1
)