Oracle searches for and deletes duplicate records in a table

Source: Internet
Author: User

Oracle searches for and deletes repeated records in a table: a field in an application table is a primary key. when inserting data into a table, the data is first placed in a temporary table (without a primary key) then insert the application table. At this time, if duplicate data exists in the temporary table, whether the primary key field businessid is repeated or the whole row is duplicated, an error in violation of the unique primary key constraint will be reported. Method: group by XX having count (*)> 1, rowid, distinct, temporary table, procedure1, query duplicate data in table. repeat a field B. repeat multiple fields c. repeat the entire row to create a test table: create table cfa (businessid number, customer varchar2 (50), branchcode varchar2 (10), data_date varchar2 (10); insert into cfa values (1, 'Albert ', 'scb', '2017-11-11'); insert into cfa values (2, 'andy ', 'db', '2017-11-12 '); insert into cfa values (3, 'allen ', 'HSBC', '2017-11-13 '); ------------- The following is a duplicate Data ------------------------------------------ insert into cfa values (1, 'Alex ', 'icbc', '2017-11-14'); insert into cfa values (1, 'Albert ', 'ctbk ', '2017-11-15 '); insert into cfa values (1, 'Albert', 'scb', '2017-11-11 '); for, only businessid repeated select * from cfa where businessid in (select businessid from cfa group by businessid having count (businessid)> 1); if it is B, both businessid and name have repeated select * from cfa wher E (businessid, customer) in (select businessid, customer from cfa group by businessid, customer having count (*)> 1); for c, to repeat the entire row, see Method B: select * from cfa where (businessid, customer, branchcode, data_date) in (select * from cfa group by businessid, customer, branchcode, data_date having count (*)> 1); 2. Delete duplicate data a in the table and delete redundant duplicate records in the table. duplicate records are determined based on a single field (businessid, only records with the smallest rowid can be retained, and rowid is not the smallest record. You need to change the min in the code to max. Delete from cfawhere businessid in (select businessidfrom cfagroup by businessidhaving count (businessid)> 1) and rowid not in (select min (rowid) from cfagroup by businessidhaving count (businessid)> 1 ); alternatively, use the following simple and efficient statement delete from cfa tWHERE t. ROWID> (select min (X. ROWID) FROM cfa x where x. businessid = t. businessid); B, delete unnecessary duplicate records (multiple fields) in the table, with only the records with the smallest rowid deleted from cfawhere (businessid, customer) in (select businessid, customerfrom cfagroup by businessid, customerhaving count (*)> 1) and rowid not in (select min (rowid) from cfagroup by businessid, customerhaving count (*)> 1); or, use the following simple and efficient statement delete from cfa tWHERE t. ROWID> (select min (X. ROWID) FROM cfa xwhere x. businessid = t. businessidand x. customer = t. customer); c, this situation is relatively simple, use the temporary table method create table cfabak as select distinct * from cfa; truncate table cfa; -- for production, it is best to perform backupInsert into cfa select * from cfabak; commit;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.