How to find and delete duplicate records in a table

Source: Internet
Author: User
Tags insert
Repeat how to find and delete duplicate records in a table
Software Environment: 1, Windows nt4.0+oracle 8.0.4
2, the Oracle installation path is: C:\ORANT
Issue: 1, when we want to create a unique index for a table, if the table has duplicate records, you cannot create a success. Method principle: 1, Oracle, each record has a rowid,rowid in the entire database is unique,
ROWID determines which data files, blocks, and lines are in Oracle for each record.

2. In duplicate records, all columns may have the same content, but the ROWID will not be the same, so just make sure that the duplicate records
Those with the biggest rowid can be, and all the rest is removed.

3, the following statements used 3 skills: ROWID, subqueries, aliases.
Implementation method:
Sql> CREATE Table A (2 BM char (4),--encoded 3 MC VARCHAR2 (20)--Name 4) 5/table has been established. Sql> INSERT into a values (' 1111 ', ' 1111 '); Sql> INSERT into a values (' 1112 ', ' 1111 '); Sql> INSERT into a values (' 1113 ', ' 1111 '); Sql> INSERT into a values (' 1114 ', ' 1111 '); sql> INSERT into a select * from A; Insert 4 records. Sql> commits a full submission. Sql> select ROWID,BM,MC from A; ROWID BM MC-----------------------------000000d5.0000.0002 1111 1111000000d5.0001.0002 1112 1111000000d5.0002.0002 1113 1111000000d5.0003.0002 1114 1111000000d5.0004.0002 1111 1111000000d5.0005.0002 1112 1111000000D5.0006.0002 1113 1111000000d5.0007.0002 1114 1111 Query to 8 records. Detect duplicate records sql> Select ROWID,BM,MC from a where a.rowid!= (select Max (ROWID) from a B where A.BM=B.BM and A.MC=B.MC); ROWID BM MC------------------------------------------000000d5.0000.0002 1111 1111000000d5.0001.0002 1112 1111000000d5.0002.0002 1113 1111000000d5.0003.0002 1114 1111 Delete duplicate records sql> Delete from a where a.rowid!= (select Max (rowi D) from a b where a.bm=b.bm and a. mc=b.mc); Delete 4 records. Sql> select ROWID,BM,MC from A; ROWID BM MC------------------------------------------000000d5.0004.0002 1111 1111000000d5.0005.0002 1112 1111000000d5.0006.0002 1113 1111000000d5.0007.0002 1114 1111


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.