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
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.
