Oracle Query (delete) duplicate records

2010-08-13  来源:本站原创  分类:Database  人气:182 

First, the Oracle database query so-called "duplicate" records, according to criterion can be broken down into three cases:

1. According to a single attribute field judge repeatability:

select *
from tableName
where columnName in (select columnName
from tableName
group by columnName
having count (columnName)> 1);

2. According to multiple attribute fields determine repeatability:

select *
from tableName a
where (a.columnName1, a.columnName2) in (select columnName1, columnName2
from tableName
group by columnName1, columnName2
having count (*)> 1);

3. All the attributes exactly the same situation:

select distinct * from td_tyorg;

Using the above SQL statement can not query the same record

Check duplicate records --- ---

select * from tablename where id in (
select id from tablename
group by id
having count (id)> 1;

Second, delete the duplicate records:

1. Remove a field the same record (the record, leaving only the smallest rowid, rowid for the oracle system in which self-built field):

delete from people
where peopleId in (select peopleId
from people
group by peopleId
having count (peopleId)> 1)
and rowid not in (select min (rowid)
from people
group by peopleId
having count (peopleId)> 1);

4, delete unwanted duplicate records in the table (multiple fields), leaving only the smallest rowid record:

delete from vitae a
where (a.peopleId, a.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);

PS: If the table need to remove duplicate records (records to retain a duplicate), can be removed by the following methods
select distinct *
into # Tmp
from tableName drop table tableName
select * into tableName from # Tmp drop table # Tmp

相关文章
  • Oracle Query (delete) duplicate records 2010-08-13

    First, the Oracle database query so-called "duplicate" records, according to criterion can be broken down into three cases: 1. According to a single attribute field judge repeatability: select * from tableName where columnName in (select columnN

  • ORACLE and delete duplicate records query methods 2010-11-09

    (A) 1, the lookup table in the extra duplicate records, duplicate records are based on a single field (peopleId) to determine select * from people where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1) 2, delete

  • Oracle to delete duplicate records 2010-09-07

    After testing, the most efficient one: (15 000 data, about 0.125s) delete from tableName le where le.rowid not in (Select MAX (a.rowid) from tableName a GROUP BY a. Field 1, a. Field 2, ...)

  • Oracle query and delete duplicate records in SQL statement collection 2010-03-25

    Oracle query and delete duplicate records in SQL statement collection ORACLE query and delete duplicate data, we often encounter problems and need treatment, although often useful in that way a lot. But do not want to sort out, so reposted elsewhere

  • Oracle query and SQL statement to delete duplicate records 2010-10-04

    Query and the SQL statement to delete duplicate records Query and the SQL statement to delete duplicate records 1, the lookup table in the extra duplicate records, duplicate records are based on a single field (peopleId) to determine select * from pe

  • Oracle query and delete duplicate records in SQL statements 2010-05-04

    Inquiries and the SQL statement to delete duplicate records 1, find the table in excess of duplicate records, duplicate records are based on a single field (peopleId) to determine select * from people where peopleId in (select peopleId from people gr

  • Using SQL statement to query the table and delete duplicate records 2011-04-30

    Article I: Suppose, as a database table TNames, has two fields ID: primary key int; Name: nvarchar (50) TNames table there is some duplication of data lines such as: ID Name --------------------------- 1 wang 2 wang 3 lee 4 lee 5 lee 6 zhang 7 zhang

  • Query and delete duplicate records SQL statements (change) 2010-04-02

    Inquiries and the SQL statement to delete duplicate records 1, find the table in excess of duplicate records, duplicate records are based on a single field (peopleId) to determine select * from people where peopleId in (select peopleId from people gr

  • Oracle using Rowid find and delete duplicate records 2010-08-18

    Routine work may encounter when attempting a database table column or columns to create a unique index, the system prompted ORA-01452: can not create unique index, find duplicate records. The following summary to find and delete duplicate records sev

  • MySQL query to delete duplicate records and the method 2011-02-15

    MySQL query to delete duplicate records and the method Query, and delete duplicate records method (A) 1, the lookup table in excess of duplicate records, duplicate records are based on a single field (peopleId) to determine select * from people where

  • Delete duplicate records oracle method 2011-08-25

    A project when a colleague guide data when a table is not accidentally all the data out heavy, that is, all records in this table has a duplicate. The data in this table is tens of millions, and the production system. In other words, not all records

  • MySQL query to delete duplicate records and the methods 2011-05-17

    Inquiries and the method to delete duplicate records (A) 1, the lookup table in the extra duplicate records, duplicate records are based on a single field (peopleId) to determine select * from people where peopleId in (select peopleId from people gro

  • sql server query, and delete duplicate records method 2010-12-22

    First, according to the duplication of a single field 1, the lookup table in excess of duplicate records, duplicate records are based on a single field (peopleId) to determine select * from people where peopleId in (select peopleId from people group

  • sql delete duplicate records 2010-04-07

    For example: id name value 1 a pp 2 a pp 3 b iii 4 b pp 5 b pp 6 c pp 7 c pp 8 c iii id is the primary key requirements are the result of this id name value 1 a pp 3 b iii 4 b pp 6 c pp 8 c iii Method 1 delete YourTable where [id] not in ( select max

  • Several SQL statements to delete duplicate records 2010-04-27

    Example, officers now have a table (table name: peosons) If you want the name, ID number, address these three fields exactly the same record check out select p1 .* from persons p1, persons p2 where p1.id <> p2.id and p1.cardid = p2.cardid and p1.pna

  • Delete duplicate records using SQL methods N 2010-12-03

    For example: id name value 1 a pp 2 a pp 3 b iii 4 b pp 5 b pp 6 c pp 7 c pp 8 c iii id is the primary key requirements are the result of this id name value 1 a pp 3 b iii 4 b pp 6 c pp 8 c iii Method 1 delete YourTable where [id] not in ( select max

  • sql statement to delete duplicate records method 2011-07-29

    For example: id name value 1 a pp 2 a pp 3 b iii 4 b pp 5 b pp 6 c pp 7 c pp 8 c iii id is the primary key requirement to get this result id name value 1 a pp 3 b iii 4 b pp 6 c pp 8 c iii Method 1 delete YourTable where [id] not in ( select max([id]

  • Delete duplicate records using SQL methods of N 2010-12-03

    For example: id name value 1 a pp 2 a pp 3 b iii 4 b pp 5 b pp 6 c pp 7 c pp 8 c iii id is the primary key requirement to get this result id name value 1 a pp 3 b iii 4 b pp 6 c pp 8 c iii Method 1 delete YourTable where [id] not in ( select max ([id

  • Delete duplicate records using SQL methods of N (rpm) 2011-02-27

    For example: id name value 1 a pp 2 a pp 3 b iii 4 b pp 5 b pp 6 c pp 7 c pp 8 c iii id is the primary key requirement to get this result id name value 1 a pp 3 b iii 4 b pp 6 c pp 8 c iii Method 1 delete YourTable where [id] not in ( select max ([id

  • Reprinted: SQL to delete duplicate records with the N ways 2011-05-31

    State: The Article from http://blog.csdn.net/jeefchen/archive/2009/06/05/4246110.aspx (non-original) For example: id name value 1 a pp 2 a pp 3 b iii 4 b pp 5 b pp 6 c pp 7 c pp 8 c iii id is the primary key requirement to get this result id name val