How to find, delete repetitive records in the table
Software Environment:
1, Windows NT4.0 Oracle 8.0.4
2, the Oracle installation path is: C: / ORANT
Questions raised:
1. When we want to create a unique index for a table, if the table has a repetitive record, it cannot be created.
Method principle:
1, Oracle, each record has a RowID, and RowID is unique in the entire database.
RowID determines which data file, block, and rows of each record are in Oracle.
2. In the repeated record, it may be the same content, but the RowID will not be the same, so as long as you determine the repeated record
Those who have the largest ROWID can be removed, and the rest is removed.
3, the following statement is used to three skills: RowID, child query, alias.
Implementation method: SQL> CREATE TABLE A.
2 BM Char (4), - Code
3 mc varchar2 (20) - Name
4)
5 /
The 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> commit;
Completely submit.
SQL> SELECT ROWID, BM, MC from A;
Rowid BM MC
---------------------------
000000D5.0000.0002 1111 1111
000000D5.0001.0002 1112 1111
000000D5.0002.0002 1113 1111
000000D5.0003.0002 1114 1111
000000D5.0004.0002 1111 1111
000000D5.0005.0002 1112 1111
000000D5.0006.0002 1113 1111
000000D5.0007.0002 1114 1111
Query 8 records.
Isolated repeated record
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 1111
000000D5.0001.0002 1112 1111
000000D5.0002.0002 1113 1111
000000D5.0003.0002 1114 1111
Delete repeat
SQL> Delete from a a a where a.rowid! = (Select max (rowid) 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 1111
000000D5.0005.0002 1112 1111
000000D5.0006.0002 1113 1111
000000D5.0007.0002 1114 1111
