How to query duplicate data and delete duplicate records in Oracle

How to query duplicate data and delete duplicate records in Oracle

For example, there is a personnel table (table name: peosons) drop table PERSONS; create table PERSONS (PNAME VARCHAR2 (50), CARDID VARCHAR2 (18), ADDRESS VARCHAR2 (100 )); insert into persons (PNAME, CARDID, ADDRESS) values ('zhang san', '123', 'shenzhen '); insert into persons (PNAME, CARDID, ADDRESS) values ('Lee 4', '123', 'shenzhen '); insert into persons (PNAME, CARDID, ADDRESS) values ('wang 5', '123', 'shenzhen '); insert in To persons (PNAME, CARDID, ADDRESS) values ('zhang san', '123', 'shenzhen '); insert into persons (PNAME, CARDID, ADDRESS) values ('zhao liu', '123', 'shenzhen '); insert into persons (PNAME, CARDID, ADDRESS) values ('zhao liu', '123 ', 'shenzhen '); insert into persons (PNAME, CARDID, ADDRESS) values ('plum', '123', 'shenzhen '); insert into persons (PNAME, CARDID, ADDRESS) values ('small Plum ', '000000', 'shenzhen'); insert into persons (PNAME, CARDID, ADDRESS) values ('zhangzhangzi', '000000', 'shenzhen '); insert into persons (PNAME, CARDID, ADDRESS) values ('zhangzhangzi', '123', 'shenzhen'); commit; select p1. * from persons p1, persons p2 where p1.rowid <> p2.rowid and p1.cardid = p2.cardid and p1.pname = p2.pname and p1.address = p2.address; SQL> sel Ect p1. * 2 from persons p1, persons p2 3 where p1.rowid <> p2.rowid 4 and p1.cardid = p2.cardid 5 and p1.pname = p2.pname 6 and p1.address = p2.address; pname cardid address -------------------- ---------------- -------------------- Zhang San 430682199002121010 Shenzhen Zhang San 430682199002121010 Shenzhen Zhao Liu 430682199002121011 Shenzhen Zhao Liu 430682199002121011 Shenzhen Xiao Zhangzi 430682199002121013 Shenzhen Xiao Zhangzi 430682199002121013 Shenzhen has selected 6 lines. The above results can be achieved. Several SQL statements for deleting duplicate records 1. use rowid method 2. use the group by method 3. use distinct method 1. use the rowid method to judge whether there are duplicates according to the rowid attribute of oracle. The statement is as follows: select * from persons a where rowid! = (Select max (rowid) from persons B where. pname = B. pname and. cardid = B. cardid and. address = B. address); SQL> select * 2 from persons a 3 where rowid! = (Select max (rowid) from persons B 4 where. pname = B. pname 5 and. cardid = B. cardid 6 and. address = B. address); pname cardid address -------------------- ------------------ -------------------- Zhang San 430682199002121010 Shenzhen Zhao Liu 430682199002121011 Shenzhen xiaozhangzi 430682199002121013 Shenzhen deletes duplicate data and retains the maximum rowid delete from persons a where rowid! = (Select max (rowid) from persons B where. pname = B. pname and. cardid = B. cardid and. address = B. address); 2. group by method example: query the repeated select count (pname), max (pname) from persons -- list the number of repeated records, and list its name attribute group by pname -- After grouping by panme, find the repeated pname columns in the table, that is, the number of occurrences is greater than one having count (*)> 1SQL> select count (pname ), max (pname) 2 from persons 3 group by pname 4 having count (*)> 1; COUNT (PNAME) MAX (PNAME )------------- ------------------------------------------- 2 Zhao liu2 Xiao Zhangzi 2 Xiao Lizi 2 Zhang San delete data from persons where pname in (select pname from persons group by pname having count (*)> 1); example: query multiple fields repeated SELECT * from persons a where (. PNAME,. CARDID,. ADDRESS) IN (select pname, CARDID, address from persons group by pname, CARDID, address having count (*)> 1); Delete redundant record (multiple fields) IN the table ), only records with the smallest rowid are record DELETE FROM PERSONS A WHE RE (. PNAME,. CARDID,. ADDRESS) IN (select pname, CARDID, address from persons group by pname, CARDID, address having count (*)> 1) and rowid not in (select min (ROWID) from persons group by pname, CARDID, address having count (*)> 1); query redundant duplicate records in the table (multiple fields ), SELECT * from persons a where (. PNAME,. CARDID,. ADDRESS) IN (select pname, CARDID, address from persons group by pname, CARDID, Ddress having count (*)> 1) and rowid not in (select min (ROWID) from persons group by pname, CARDID, address having count (*)> 1 ); SQL> SELECT * FROM PERSONS A 2 WHERE (. PNAME,. CARDID,. ADDRESS) IN 3 (select pname, CARDID, ADDRESS 4 from persons 5 group by pname, CARDID, ADDRESS 6 having count (*)> 1) AND 7 rowid not in (select min (ROWID) 8 from persons 9 group by pname, CARDID, ADDRESS 10 HAVI Ng count (*)> 1 ); pname cardid address -------------------- Xiao Zhangzi 430682199002121013 Shenzhen Zhao Liu 430682199002121011 Shenzhen Xiao Lizi 430682199002121011 Shenzhen Zhao 6 430682199002121011 Shenzhen Xiao Lizi 430682199002121012 Shenzhen Xiao Zhangzi 430682199002121013 Shenzhen Zhang 3 430682199002121010 Shenzhen Wang 5 430682199002121010 Shenzhen LI 4 430682199002121010 Shenzhen 10 rows have been selected.

 

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.