Oracle deletes duplicate rows

SQL statement 1 for Oracle to delete duplicate rows and query and delete duplicate records. It is used to find redundant duplicate records in the Table. duplicate records are based on a single field (peopleId) select * from peoplewhere peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1) 2. Delete unnecessary duplicate records in the table, repeat records are determined based on a single field (peopleId). Only the records with the smallest rowid are retained: delete from peoplewhere peopleId in (select peopleId from people group by leleid having count (peopleId)> 1) and rowid not in (select min (rowid) from people group by distinct L EId having count (peopleId)> 1) Note: rowid is not required for Oracle ..... 3. Search for redundant duplicate records in the table (multiple fields) select * from vitae awhere (. peopleId,. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1) 4. Delete redundant record (multiple fields) in the table ), delete from vitae awhere (. peopleId,. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1) and rowid not in (select min (rowid) from vitae g Roup by distinct leid, seq having count (*)> 1) 5. Search for redundant duplicate records in the table (multiple fields), excluding select * from vitae awhere (. peopleId,. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1) and rowid not in (select min (rowid) from vitae group by peopleId, seq having count (*)> 1) (2) for example, there is A field "name" in Table A, and the "name" values may be the same between different records, now, you need to query the records in the table, and the "name" value has repeated items; Select Name, Count (*) From A Group Name Having Count (*)> 1 if the sex is also the same, Select Name, sex, Count (*) From A Group By Name, sex Having Count (*)> 1 (3) method 1 declare @ max integer, @ id integerdeclare cur_rows cursor local for select Main field, count (*) from table name group by main field having count (*)>; 1 open cur_rowsfetch cur_rows into @ id, @ maxwhile @ fetch_status = 0 beginselect @ max = @ max-1 set rowcount @ maxdelete from table name where primary field = @ idfetch cur_rows into @ id, @ Maxendclose cur_rowsset rowcount 0 method 2 "Repeat record" has two duplicate records. One is a completely repeated record, that is, a record with all fields already exists, second, some records with duplicate key fields, such as duplicate Name fields, are not necessarily repeated or can be ignored. 1. For the first type of repeat, it is easy to solve. You can use select distinct * from tableName to obtain the result set without repeated records. If the table needs to delete duplicate records (one record is retained ), you can delete select distinct * into # Tmp from tablenameddrop table tableNameselect * into tableName from # Tmpdrop table # Tmp. This duplication occurs because the table is not designed for weeks, you can add a unique index column. 2. This type of repetition problem usually requires that the first record in the repeat record be retained. The operation method is as follows, assuming that there are repeated fields: Name, Address, select identity (int, 1, 1) as autoID, * into # Tmp from tableNameselect min (autoID) as autoID into # Tmp2 from # Tmp group by Name, autoIDselect * from # Tmp where autoID in (select autoID from # tmp2) The last select gets the Name, the Address does not repeat the result set (but an autoID field is added, this column can be omitted in the select clause during actual writing) (4) query repeated select * from tablename where id in (select id from tablenamegr Oup by idhaving count (id)> 1) =======================================================1. Use the rowid method to determine whether there are duplicates according to the rowid attribute of Oracle. The statement is as follows: query data: select * from table1 a where rowid! = (Select max (rowid) from table1 B where. name1 = B. name1 and. name2 = B. name2 ......) delete data: delete from table1 a where rowid! = (Select max (rowid) from table1 B where. name1 = B. name1 and. name2 = B. name2 ......) 2. query data using the group by method: select count (num), max (name) from student -- lists the number of repeated records and its name attribute group by numhaving count (num)> 1 -- repeat the num column in the table after grouping by num, that is, more than one data deletion occurs: delete from studentgroup by numhaving count (num)> 1. In this way, all repeated items are deleted. 3. Use the distinct method-for small tables, create table table_new as select distinct * from table1 minuxtruncate table table1; insert into table1 select * from table_new;