Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: Duplicate Records

    Hi All,

    How to remove the duplicate records from the table? Thanks.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How to remove the duplicate records from the table?
    By using the DELETE statement
    P.S.
    GOOGLE is your friend, but only if you take the time to use it.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    We can help, but more information is needed. What is the structure of the table? What makes up a duplicate record. If you mean every column in the table. What constraints are on the table? How big is the table. If it is small, the following quick and dirty will work.

    create table my_temp_table as select distinct * from my_real_table;

    delete from my_real_table;

    insert into my_real_table as select * from my_temp_table;

    commit;

    drop my_temp_table;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Oct 2004
    Posts
    7
    beilstwh is right, you need to be concerned about contsraints and triggers and such on your table.
    But assuming you know how to take care of that, another way to get rid of duplicate rows is to use the pseudocolumn rowid:
    Code:
    16:13:14 TERTIUS@test > create table test (
    17:06:00 TERTIUS@test > id    NUMBER,
    17:06:11 TERTIUS@test > name  VARCHAR2(30)
    17:06:22 TERTIUS@test > );
    
    Table created.
    
    17:06:25 TERTIUS@test > insert into test values(1, 'Luke');
    
    1 row created.
    
    
    17:06:48 TERTIUS@test > /
    
    1 row created.
    
    17:06:50 TERTIUS@test > select * from test;
    
            ID NAME
    ---------- ------------------------------
             1 Luke
             1 Luke
    
    17:07:04 TERTIUS@test > select id,name,rowid
    17:07:17 TERTIUS@test > from test;
    
            ID NAME                           ROWID
    ---------- ------------------------------ ------------------
             1 Luke                           AAGcM9AAEAAAlfOAAA
             1 Luke                           AAGcM9AAEAAAlfOAAB
    
    17:07:21 TERTIUS@test > delete from test where rowid = 'AAGcM9AAEAAAlfOAAA';
    
    1 row deleted.
    
    17:07:37 TERTIUS@test > select * from test;
    
            ID NAME
    ---------- ------------------------------
             1 Luke
    Last edited by tertiusluke; 02-23-05 at 18:28.

  5. #5
    Join Date
    Oct 2004
    Posts
    7
    Taking this a little further to get rid of all duplicate rows in a table. Try this:
    Code:
    18:04:44 TERTIUS@test > create table test (
    18:04:49 TERTIUS@test > id    NUMBER,
    18:04:49 TERTIUS@test > name  VARCHAR2(30)
    18:04:49 TERTIUS@test > );
    
    Table created.
    
    18:04:49 TERTIUS@test > insert into test values(1, 'Luke');
    
    1 row created.
    
    18:04:53 TERTIUS@test > /
    
    1 row created.
    
    18:04:54 TERTIUS@test > /
    
    1 row created.
    
    18:04:55 TERTIUS@test > insert into test values(7, 'Leslie');
    
    1 row created.
    
    18:04:59 TERTIUS@test > /
    
    1 row created.
    
    18:05:00 TERTIUS@test > select * from test;
    
            ID NAME
    ---------- ------------------------------
             1 Luke
             1 Luke
             1 Luke
             7 Leslie
             7 Leslie
    
    18:05:05 TERTIUS@test > create table test_rowids as
    18:05:17 TERTIUS@test > select MIN(a.id)    r_id,
    18:05:17 TERTIUS@test >        MIN(a.name)  r_name,
    18:05:17 TERTIUS@test >        MIN(a.rowid) r_rowid
    18:05:17 TERTIUS@test >   from test a,
    18:05:17 TERTIUS@test >        test b
    18:05:17 TERTIUS@test >  where a.id = b.id
    18:05:17 TERTIUS@test >    and a.name = b.name
    18:05:17 TERTIUS@test >    and a.rowid <> b.rowid
    18:05:17 TERTIUS@test >  group by a.id,
    18:05:17 TERTIUS@test >           a.name
    18:05:19 TERTIUS@test > /
    
    Table created.
    
    18:05:21 TERTIUS@test > select * from test_rowids;
    
          R_ID R_NAME                         R_ROWID
    ---------- ------------------------------ ------------------
             1 Luke                           AAGcX0AAEAAAlfOAAA
             7 Leslie                         AAGcX0AAEAAAlfOAAD
    
    18:05:26 TERTIUS@test > delete from test
    18:05:40 TERTIUS@test > where EXISTS
    18:05:40 TERTIUS@test > (select 1
    18:05:40 TERTIUS@test >    from test_rowids
    18:05:40 TERTIUS@test >   where test_rowids.r_id    =  test.id
    18:05:40 TERTIUS@test >     and test_rowids.r_name  =  test.name
    18:05:40 TERTIUS@test >     and test_rowids.r_rowid <> test.rowid)
    18:05:43 TERTIUS@test > /
    
    3 rows deleted.
    
    18:05:48 TERTIUS@test > select * from test;
    
            ID NAME
    ---------- ------------------------------
             1 Luke
             7 Leslie
    
    18:05:53 TERTIUS@test > drop table test_rowids;
    
    Table dropped.

  6. #6
    Join Date
    Mar 2004
    Location
    Warsaw / Poland
    Posts
    73
    Code:
    DELETE FROM table_name
      WHERE ROWID IN (
        SELECT rid
          FROM (
            SELECT ROWID AS rid, ROW_NUMBER() OVER (PARTITION BY unique_columns_list ORDER BY ROWID) AS rn 
              FROM table_name
          )
          WHERE rn > 1
      );
    If you want to remove duplicated rows you need to put all columns for unique_columns_list.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •