8 Replies Latest reply on Aug 3, 2011 9:11 PM by orawiss

    Delete duplicate records from table

    874450
      CREATE GLOBAL TEMPORARY TABLE MyTable1 (
      Name1 varchar2(100)

      );

      insert into MyTable1 values ('11');
      insert into MyTable1 values ('11');
      insert into MyTable1 values ('11');
      insert into MyTable1 values ('11');
      insert into MyTable1 values ('12');
      insert into MyTable1 values ('12');

      select * from MyTable1

      I want to delete duplicate names from MyTable1..And after delete I should have 11 and 22 only.

      I can do this using below query but it seems to be not efficient way. Is there any other way to do this?

      delete from MyTable1
      where rowid not in
      (select max(rowid)
      from MyTable1
      group by Name1
      )

      Edited by: 871447 on Aug 3, 2011 12:47 PM
        • 1. Re: Delete duplicate records from table
          Please define 'not efficient'
          Also in a correct design tables have no duplicates.
          Temporary tables are usually not required in Oracle.
          If you need temporary tables likely you are doing it wrong.
          You seem to come from a sqlserver background
          - does not read documentation
          - tries to port sqlserver code to Oracle.

          Both approaches will fail.

          ---------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: Delete duplicate records from table
            orawiss
            SQL> CREATE GLOBAL TEMPORARY TABLE MyTable1 (
              2  Name1 varchar2(100)
              3  );
            
            Table created.
            
            SQL> CREATE GLOBAL TEMPORARY TABLE MyTable2 (
              2  Name1 varchar2(100) PRIMARY KEY
              3  );
            
            Table created.
            
            SQL>
            insert into MyTable1 values ('11');
            
            1 row created.
            
            insert into MyTable1 values ('11');
            
            1 row created.
            
            insert into MyTable1 values ('11');
            
            1 row created.
            
            insert into MyTable1 values ('11');
            
            1 row created.
            
            insert into MyTable1 values ('12');
            
            1 row created.
            
            SQL> insert into MyTable1 values ('12');
            
            1 row created.
            
            SQL> insert into MyTable2 select distinct name1 from Mytable1;
            
            2 rows created.
            
            SQL> select * from mytable2;
            
            NAME1
            --------------------------------------------------------------------------------
            11
            12
            
            SQL> 
            • 3. Re: Delete duplicate records from table
              874450
              I am from sql server background. But what if there is need to delete duplicate records from table especially that table is loaded using sqlldr and ctl file and that file is having some duplicate records.
              Even if its not temp table but query remains the same and I put sample code to get the efficient query.
              • 4. Re: Delete duplicate records from table
                mseberg
                There's an Ask Tom on this subject that might help :

                http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15258974323143

                mseberg
                • 5. Re: Delete duplicate records from table
                  585179
                  871447 wrote:

                  I want to delete duplicate names from MyTable1..And after delete I should have 11 and 22 only.

                  I can do this using below query but it seems to be not efficient way. Is there any other way to do this?

                  How do you know it's not "efficient" ?

                  alternative use below example
                  DELETE FROM MyTable1 a  
                  WHERE ROWID <> (SELECT MAX(ROWID)  
                                  FROM MyTable b 
                                  WHERE a.name1 = b.name1); 
                   
                  or
                   
                  DELETE FROM MyTable1 a 
                  WHERE 1 < (SELECT COUNT(deptno)  
                             FROM MyTable1 b 
                             WHERE a.name1 = b.name1); 
                  Hope this helps


                  CHeers
                  • 6. Re: Delete duplicate records from table
                    orawiss
                    871447 wrote:
                    I am from sql server background. But what if there is need to delete duplicate records from table especially that table is loaded using sqlldr and ctl file and that file is having some duplicate records.
                    Even if its not temp table but query remains the same and I put sample code to get the efficient query.
                    You can use External tables from 9i, from the External tables you read directly from the files. In The query reading from the file you can always use the key distinct to avoid having duplicated.

                    http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/et_concepts.htm
                    • 7. Re: Delete duplicate records from table
                      874450
                      delete from Mytable1
                      where rowid IN ( select rid
                      from (select rowid rid,
                      row_number() over (partition by
                      Name1
                      order by rowid) rn
                      from Mytable1)
                      where rn <> 1);
                      • 8. Re: Delete duplicate records from table
                        orawiss
                        871447 wrote:
                        delete from Mytable1
                        where rowid IN ( select rid
                        from (select rowid rid,
                        row_number() over (partition by
                        Name1
                        order by rowid) rn
                        from Mytable1)
                        where rn <> 1);
                        Did you looked to the external tables solution I suggested? Anyway, at the end, that's your database --- you do what you want!