Oracle queries duplicate data and delete, leaving only one record ____oracle

Oracle queries duplicate data and delete, leaving only one record

Preface

In the project, in the resource catalog-online catalog, there are multiple postback data in the Resource Item child table, which requires data cleanup, deletion of the postback data, and ultimately only one identical data.

Table name for action: r_resource_details

Operation Steps

One, duplicate records are judged by a single field

1, first of all, to find redundant records in the table, duplicate records are based on a single field (Field_code) to judge

SELECT * from R_resource_details where Field_code in (select Field_code to R_resource_details GROUP by has count (FIELD _code) >1)

2, delete redundant records in the table, duplicate records are based on a single field (Field_code) to judge, leaving only rowid minimal records

Delete from R_resource_details where (Field_code) in (select Field_code to R_resource_details GROUP by Field_code has Count (Field_code) >1) and rowID not in (select min (rowid) from R_resource_details GROUP by Field_code have Count (*) & GT;1)

second, repeat records based on multiple fields to determine

1. Find redundant records in the table (multiple fields)

SELECT * from R_resource_details a WHERE (a.field_code,a.dta_item_name) in (select Field_code,dta_item_name from R_ Resource_details GROUP BY Field_code,dta_item_name has count (*) > 1)

2, delete redundant records in the table (multiple fields), leaving only the smallest ROWID records

Delete from R_resource_details a WHERE (a.field_code,a.dta_item_name) in (select Field_code,dta_item_name from R_ Resource_details GROUP BY Field_code,dta_item_name has count (*) > 1) and rowID not in (select min (rowid) from R_reso Urce_details GROUP BY Field_code,dta_item_name has count (*) >1)

3, look for redundant records in the table (multiple fields), does not contain the smallest ROWID records

SELECT * from R_resource_details a WHERE (a.field_code,a.dta_item_name) in (select Field_code,dta_item_name from R_resour Ce_details GROUP BY Field_code,dta_item_name has count (*) > 1) and rowID not in (select min (rowid) from R_resource_d Etails GROUP BY Field_code,dta_item_name has count (*) >1)