Oracle database to remove duplicate records in table

2011-03-17  来源:本站原创  分类:Database  人气:101 

Transfer: http://aszx0413.com/blog/?p=161

In oracle, some data exactly the same, to clear out the duplicates, leaving only one non-duplication of data.

  1. DELETE FROM tb WHERE ROWID NOT IN (
  2. SELECT MAX (ROWID) FROM tb GROUP BY col1, col2 ...
  3. )

Which col1, col2 ... to repeat the data field basis.

Another method

  1. DELETE FROM tb t1
  2. WHERE ROWID <(SELECT MAX (ROWID) FROM tb t2 WHERE t1.col = t2.colAND ...)

PS: how efficient, to be studied further.

相关文章
  • Oracle database to remove duplicate records in table 2011-03-17

    Transfer: http://aszx0413.com/blog/?p=161 In oracle, some data exactly the same, to clear out the duplicates, leaving only one non-duplication of data. DELETE FROM tb WHERE ROWID NOT IN ( SELECT MAX (ROWID) FROM tb GROUP BY col1, col2 ... ) Which col

  • Oracle database efficiently remove duplicate data in the method 2011-05-03

    REVIEW: Oracle database operation is a complex process, operation, pay special attention to details, for example, operate on the Oracle database process, we may encounter this situation, data in the table may be repeated, so that our database during

  • oracle stored procedure: remove duplicate records 2011-01-21

    create or replace procedure SP_STRIP_PK( /* Remove duplicate records ( Guaranteed primary key value uniquely ) */ p_table in varchar2, -- Physical tables partition_by in varchar2, -- Partitioning PK field order_by in varchar2 -- Sort by ) is begin EX

  • Oracle database efficiently remove duplicate data approach 2010-08-17

    Transfer from: http://www.builder.com.cn/2008/0909/1115350.shtml Operation in the database, we may encounter this situation, data in the table may be repeated so that our database during operation caused a lot of inconvenience, Name how useless to re

  • oracle method to remove duplicate records 2010-07-05

    1. With rowid Query: select * from table1 as a where rowid <> (select max (rowid) from table1 as b where a.name = b.name and .... the same field to compare the column) Delete: delete from table1 as a where rowid <> (select max (rowid) from tab

  • Idea of ​​a database to remove duplicate records 2011-06-22

    Large amount of data updates, it is best not to use the update, so to get: Step 1: create table us_publiccomment_01 nologging as select * from us_publiccomment where rowid in ( select row_id from (select t.rowid row_id, row_number () over (partition

  • 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

  • Remove duplicate records and duplicate records common method of query 2010-08-10

    Remove duplicate records and duplicate records common method of query Oracle tests and records through a table as follows: SQL> select * from testa; A AA AAA ---------- ---------- ---------- a1 aa1 aaa1 a1 aa1 aaa2 a1 aa1 aaa3 a2 aa3 aaa3 a2 aa3 aaa3

  • ORACLE query, and delete duplicate records method 2010-11-09

    (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 peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1) 2, delete

  • SQL statement used to remove duplicate records (transfer) 2011-03-23

    Huge amounts of data (over a million), some of which all fields are the same, some parts of the same field, how efficient removal of duplication? If you want to delete the phone (mobilePhone), telephone (officePhone), Mail (email) while the same data

  • To identify duplicate records t_user table 2010-08-03

    To identify duplicate records t_user table select username from t_user group by username having count (username)> 1

  • ORACLE how to remove duplicate data in the table 2011-05-27

    How to delete a table in ORACLE duplicate data in this situation we may, the original design is not a comprehensive list, resulting in duplication of data table inside the data, then, how to duplicate data delete? Duplicate data may have two such cas

  • quickly remove duplicate records oracle 2010-09-16

    oracle-fast delete duplicate records When do the project, guided data when a colleague is not accidentally all the data in a table out heavier, that is, all records in this table has a duplicate. The data in this table is 10 million, and the producti

  • Database: remove duplicate records, leaving only the line 2011-03-07

    I use the SQL statement: delete from fp_sgfpxx where fphm in (select fphm from fp_sgfpxx group by fphm having count (fphm)> 1) and rowid not in (select min (rowid) from fp_sgfpxx group by fphm having count (fphm)> 1) remove duplicate invoice numbers

  • (R) Oracle How to remove duplicate data 2011-07-07

    We may happen, the original design of a table is not comprehensive, resulting in duplication of data inside the table data, then how to duplicate data delete it? Duplication of data may have two such cases, the first table when only certain fields, l

  • Turn == oracle-quickly remove duplicate records 2010-10-18

    This previously written SQL, not a long time now, a little forgotten, today, when the data lead to repeat the data, Found a good article article, to be a record: the original text of gratitude Original Source: http://tech.163.com/06/0621/09/2K4NLFRH0

  • 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

  • oracle not distinct to duplicate records in 2010-12-14

    Query with distinct keyword can filter all the records in the same field (the same set of records), but if you want to specify a field has no effect, another distinct keyword ranking, the efficiency is very low. select distinct name from t1 to elimin

  • Oracle database connection error problem records 2011-03-04

    Oracle database was previously installed, then use the JDBC connection is successful, and can be used today to do when the Rom, and also a bunch of errors reported The error into a search online to find a saying that the Oracle listener service is no

  • Oracle database, remove duplicate records common method 2011-06-02

    Method One: delete from tb_channel a where a.rowid in (Select max (b.rowid) from tb_channle b where a.policyno = b.policyno and a.classcode = b.classcode); - This approach in more than 10 million data records will generally become very slow. Method T