Methods of querying duplicate data and deleting duplicate records in Oracle
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
andp1.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:
deletefrom table1 a where rowid !=（select max（rowid）
from table1 b where a.name1=b.name1 and a.name2=b.name2……） thanks for sharing this
Many thanks
Pages:
[1]
