How to delete duplicate records in the table because there are no restrictions in the oracle database

Oracle databases contain many duplicate items because they do not have any constraints. The question now is how to delete these repeated items. Repeat records only keep one of them. The following table creation statement: CREATETABLEmessage_s

Oracle databases contain many duplicate items because they do not have any constraints. The question now is how to delete these repeated items. Repeat records only keep one of them. The following table creation statement: CREATETABLEmessage_s

Oracle databases contain many duplicate items because they do not have any constraints. The question now is how to delete these repeated items. Repeat records only keep one of them. The following table creation statement

Create table message_student3 (stu_id integer not null, -- the TABLE does NOT have the uniqueness constraint stu_number varchar (30), stu_name VARCHAR (10) not null, stu_age NUMBER (2) not null ); insert into message_student3 VALUES (1, '20160301', 'zhangshan', 18); insert into message_student3 VALUES (2, '20160301', 'Liu bei ', 19 ); insert into message_student3 VALUES (3, '20160301', 'zhangfe', 19); insert into message_student3 VALUES (4, '20160301', 'up', 19 ); insert into message_student3 VALUES (5, '000000', 'zhouyu ', 19); insert into message_student3 VALUES (6, '000000', 'sunquan', 19 ); insert into message_student3 VALUES (7, '123456', 'zhang liao', 19); insert into message_student3 VALUES (1, '123456', 'zhang shan', 18 ); insert into message_student3 VALUES (2, '123456', 'Liu bei', 19); insert into message_student3 VALUES (3, '1234568', 'zhang fei', 19 ); insert into message_student3 VALUES (4, '123456', 'lubr', 19); insert into message_student3 VALUES (5, '123456', 'zhou Yu ', 19 ); insert into message_student3 VALUES (6, '000000', 'sunquan ', 19); insert into message_student3 VALUES (7, '000000', 'zhang liao', 19 );

Analysis: because the table does not have constraints, the fields of repeated records must be equal. However, for delete operations, either deleting all the fields or deleting based on one condition is definitely not feasible, the latter is deleted as soon as it is deleted. Therefore, the entry point for deleting duplicate items must have a different number of records. Think about rownum when we use oracle paging, so we want to use rownum to try it out. However, each new select statement has a new rownum, And the alias cannot be used before. This write may be vague, so I found some relevant information on the Internet, in fact, I should use rowid. What is the difference between rownum and rowid? The original address is:

Rownum and rowid are both pseudo columns, but they are fundamentally different. rownum assigns a logical number to each row based on the SQL query results, therefore, different SQL statements may lead to different rownum,

However, rowid is physically structured. When each record is inserted into the database, there will be a unique physical record (not changed ),,
For example, AAAMgzAAEAAAAAgAAB 7499 allen salesman 7698 1981/2/20 1600.00 300.00 30
The physical location of AAAMgzAAEAAAAAgAAB corresponds to this record. This record will not change with SQL.
Therefore, this leads to different application scenarios. We usually use rownum when querying SQL pages or looking for records within a certain range.
1. rownum
For example:
Search for records in the range of 2 to 10 (including 2 and 10 records)

Select * from (select rownum rn, a. * from emp a) twhere t. rn between 2 and 10;

Search for the top three records
Select * from emp a where rownum <3; here, we should note that the range to be searched using rownum must contain 1; Because rownum is recorded starting from 1, of course, you can find rownum and put it in a virtual table as the field of this virtual table and then query it based on the condition.
For example:

Select * from (select rownum rn, a. * from emp a) twhere t. rn> 2;

2. rowid
We often use it when processing repeated records in a table.
Therefore, we can process it according to rowid. rowid is unique and the query validity rate is very high,
Back to the initial issue, you need to delete the duplicate statements and use the following statements.

Delete from message_student3 a where rowid> (select min (rowid) from message_student3 B where a. stu_id = B. stu_id); commit;

In this way, you can.

Of course, you can also use a very primitive method, that is, to export data from a table with repeated records to another table, and then reverse it back.

Create table stu_tmp as select distinct * from stu; truncate table sut; // clear the table record insert into stu select * from stu_tmp; // Add the data in the temporary table back to the original table but if it is a stu table

This is only applicable when the order of magnitude is not large.

This article is from the "cainiao also wants to fly" blog. Please keep this source