SQL statement for querying and deleting duplicate records
1, look for redundant records in the table, duplicate records are based on a single field (Peopleid) to determine
SELECT * from People
where Peopleid in (select Peopleid from People GROUP by Peopleid have count (Peopleid) > 1)
2, delete redundant records in the table, duplicate records are based on a single field (Peopleid) to judge, leaving only the smallest ROWID records
Delete from people
where Peopleid in (select Peopleid from People GROUP by Peopleid have count (Peopleid) > 1)
and rowID not in (select min (rowid) from people GROUP by Peopleid have Count (Peopleid) >1)
Note: ROWID is not for Oracle.
3. Find redundant duplicate records (multiple fields) in the table
SELECT * FROM Vitae a
where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq to Vitae GROUP by PEOPLEID,SEQ have count (*) > 1) 4, delete redundant duplicate records in the table (multiple Fields), leaving only the smallest records of ROWID
Delete from Vitae a
where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq from Vitae GROUP by PEOPLEID,SEQ have count (*) > 1)
and rowID not in (select min (rowid) from Vitae GROUP by PEOPLEID,SEQ have Count (*) >1)
5. Find redundant duplicate records (multiple fields) in the table, not including the smallest ROWID records
SELECT * FROM Vitae a
where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq from Vitae GROUP by PEOPLEID,SEQ have count (*) > 1)
and rowID not in (select min (rowid) from Vitae GROUP by PEOPLEID,SEQ have Count (*) >1) (ii)
Say
A field "name" exists in table A,
and the "name" value may be the same between different records,
Now is the need to query out the records in the table, "name" value has duplicate entries;
Select Name,count (*) from A Group by Name have Count (*) > 1 if gender is also the same large then the following:
Select Name,sex,count (*) from A Group by Name,sex have Count (*) > 1
Three
Method one declare @max integer, @id integerdeclare cur_rows cursor Local for select main field, COUNT (*) from table name Group by main Field having count (*) >; 1open cur_rowsfetch cur_rows into @id, @maxwhile @ @fetch_status =0beginselect @max = @max -1set rowcount @maxdelet E from table name where main field = @idfetch cur_rows into @id, @maxendclose cur_rowsset RowCount 0 Method Two "duplicate records" have two meanings of duplicate records, one is a completely duplicate record, that is, all fields Duplicate records, and two duplicate records for some key fields, such as the name field repeating, and the other fields not necessarily repeating or repeating can be ignored. 1, for the first kind of repetition, it is easier to solve, using the SELECT DISTINCT * from TableName can be a result set without duplicate records. If the table needs to delete duplicate records (duplicate records retain 1), you can delete the SELECT DISTINCT * into #Tmp from Tablenamedrop table Tablenameselect * to TableName fro in the following ways M #Tmpdrop table #Tmp This duplication occurs because the table is poorly designed, and the unique index column is added to resolve. 2. This type of repetition usually requires the first record in the duplicate record to be retained, as follows, assuming that there is a duplicate field name,address, which requires that both fields have a unique result set of select Identity (int,1,1) as Autoid, * into #Tmp From Tablenameselect min (autoid) as autoid to #Tmp2 from #Tmp GROUP by Name,autoidselect * from #Tmp where autoid in (sel ECT autoid from #tmp2) The last select Name,address a result set that is not duplicated (but one more autoid field, which can be written in the SELECT clause when the actual write is omitted.column) (iv)
Query Repeat SELECT * FROM tablename where ID in (select ID from Tablenamegroup by idhaving count (ID) > 1)
SQL statement that Oracle queries and deletes duplicate records
