Methods of querying duplicate data and deleting duplicate records in Oracle

bj东方瑞通

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,
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
use rowid Method can achieve the above effect.
According to oracle With rowid Property to determine whether there is repetition. The statement is as follows:
Check the data:
select * from table1 a where rowid !=（select max（rowid）
from table1 b where a.name1=b.name1 and a.name2=b.name2……）
Delete data:
delete  from table1 a where rowid !=（select max（rowid）
from table1 b where a.name1=b.name1 and a.name2=b.name2……）

hjnn223

thanks for sharing this

丽姐

Many thanks