Removing duplicate rows from table in Oracle

Question

I m testing something in Oracle and populated a table with some sample data  but in the process I accidentally loaded duplicate records  so now I can t create a primary key using some of the columns   How can I delete all duplicate rows and leave only one of them

User · Answer

Check below scripts -    1   Create table test id int sal int      2        insert into test values 1 100           insert into test values 1 100           insert into test values 2 200           insert into test values 2 200           insert into test values 3 300           insert into test values 3 300           commit    3    select   from test        You will see here 6-records  4 run below query -      delete from     test where rowid in   select rowid from      select       rowid       row number       over        partition by id order by sal  dup     from test    where dup  gt  1     select   from test        You will see that duplicate records have been deleted  Hope this solves your query  Thanks

User · Answer

To select the duplicates only the query format can be   SELECT GroupFunction column1   GroupFunction column2        COUNT column1   column1  column2    FROM our table GROUP BY column1  column2  column3    HAVING COUNT column1   gt  1   So the correct query as per other suggestion is   DELETE FROM tablename a       WHERE a ROWID  gt  ANY  SELECT b ROWID                              FROM tablename b                             WHERE a fieldname   b fieldname                               AND a fieldname2   b fieldname2                               AND     so on   to identify the duplicate rows        This query will keep the oldest record in the database for the criteria chosen in the WHERE CLAUSE   Oracle Certified Associate  2008

User · Answer

5  solution  delete from emp where rowid in              select  rid from                   select rowid rid rank   over  partition by emp id order by rowid rn from emp                    where rn  gt  1

User · Answer

create or replace procedure delete duplicate enq as     cursor c1 is     select       from enquiry  begin     for z in c1 loop         delete enquiry         where enquiry enquiryno   z enquiryno         and rowid  gt  any          select rowid         from enquiry         where enquiry enquiryno   z enquiryno       end loop   end delete duplicate enq

User · Answer

For best performance  here is what I wrote    see execution plan   DELETE FROM your table WHERE rowid IN     select t1 rowid from your table  t1       LEFT OUTER JOIN         SELECT MIN rowid  as rowid  column1 column2  column3       FROM your table        GROUP BY column1  column2  column3      co1 ON  t1 rowid   co1 rowid    WHERE co1 rowid IS NULL

User · Answer

delete from dept where rowid in        select rowid      from dept      minus      select max rowid       from dept      group by DEPTNO  DNAME  LOC

User · Answer

Solution 1   delete from emp where rowid not in  select max rowid  from emp group by empno     Solution 2   delete from emp where rowid in                                   select rid from                                         select rowid rid                        row number   over partition by empno order by empno  rn                       from emp                                     where rn  gt  1                     Solution 3   delete from emp e1          where rowid not in            select max rowid  from emp e2            where e1 empno   e2 empno

User · Answer

DELETE FROM tableName  WHERE ROWID NOT IN  SELECT   MIN  ROWID  FROM table GROUP BY columnname

User · Answer

create table t2  as select distinct   from t1

User · Answer

From Ask Tom  delete from t  where rowid IN   select rid                     from  select rowid rid                                    row number   over  partition by                           companyid  agentid  class   status  terminationdate                                    order by rowid  rn                             from t                     where rn  lt  gt  1      fixed the missing parenthesis

User · Answer

create table abcd id number 10  name varchar2 20    insert into abcd values 1  abc    insert into abcd values 2  pqr     insert into abcd values 3  xyz    insert into abcd values 1  abc    insert into abcd values 2  pqr    insert into abcd values 3  xyz     select   from abcd id  Name 1   abc 2   pqr 3   xyz 1   abc 2   pqr 3   xyz  Delete Duplicate record but keep Distinct Record in table   DELETE  FROM abcd a WHERE ROWID  gt   SELECT MIN ROWID  FROM abcd b WHERE b id a id     run the above query 3 rows delete   select   from abcd  id  Name  1   abc 2   pqr 3   xyz

User · Answer

1  solution  delete from emp     where rowid not in      select max rowid  from emp group by empno     2  sloution  delete from emp where rowid in                                   select rid from                                         select rowid rid                        row number   over partition by empno order by empno  rn                       from emp                                     where rn  gt  1                     3 solution   delete from emp e1          where rowid not in            select max rowid  from emp e2            where e1 empno   e2 empno       4  solution   delete from emp where rowid in                            select rid from                                     select rowid rid                    dense rank   over partition by empno order by rowid                   rn              from emp                where rn  gt  1

User · Answer

Use the rowid pseudocolumn   DELETE FROM your table WHERE rowid not in  SELECT MIN rowid  FROM your table GROUP BY column1  column2  column3     Where column1  column2  and column3 make up the identifying key for each record  You might list all your columns

User · Answer

Solution 4   delete from emp where rowid in                            select rid from                                     select rowid rid                    dense rank   over partition by empno order by rowid                   rn              from emp                where rn  gt  1

User · Answer

DELETE from table name where rowid not in  select min rowid  FROM table name group by column name     and you can also delete duplicate records in another way  DELETE from table name a where rowid  gt   select min rowid  FROM table name b where a column b column

User · Answer

From DevX com   DELETE FROM our table WHERE rowid not in  SELECT MIN rowid  FROM our table GROUP BY column1  column2  column3         Where column1  column2  etc  is the key you want to use

User · Answer

The Fastest way for really big tables   Create exception table with structure below  exceptions table  ROW ID ROWID OWNER VARCHAR2 30  TABLE NAME VARCHAR2 30  CONSTRAINT VARCHAR2 30   Try create a unique constraint or primary key which will be violated by the duplicates  You will get an error message because you have duplicates  The exceptions table will contain the rowids for the duplicate rows   alter table add constraint unique --or primary key  dupfield1 dupfield2  exceptions into exceptions table   Join your table with exceptions table by rowid and delete dups  delete original dups where rowid in  select ROW ID from exceptions table    If the amount of rows to delete is big  then create a new table  with all grants and indexes  anti-joining with exceptions table by rowid and rename the original table into original dups table and rename new table with no dups into original table  create table new table with no dups AS       select field1  field2               from original dups t1     where not exists   select null from exceptions table T2 where t1 rowid   t2 row id

User · Answer

DELETE FROM tablename a       WHERE a ROWID  gt  ANY  SELECT b ROWID                              FROM tablename b                             WHERE a fieldname   b fieldname                               AND a fieldname2   b fieldname2

User · Answer

solution    delete from emp where rowid in       select rid from               select rowid rid          row number   over partition by empno order by empno  rn         from emp           where rn  gt  1

User · Answer

You should do a small pl sql block using a cursor for loop and delete the rows you don t want to keep   For instance   declare prev var my table var1 TYPE   begin  for t in  select var1 from my table order by var 1  LOOP  -- if previous var equal current var  delete the row  else keep on going  end loop   end

User · Answer

I didn t see any answers that use common table expressions and window functions  This is what I find easiest to work with    DELETE FROM  YourTable WHERE  ROWID IN      WITH Duplicates           AS  SELECT                ROWID RID                  ROW NUMBER                   OVER                 PARTITION BY First Name  Last Name  Birth Date                    AS RN                SUM 1                 OVER                 PARTITION BY First Name  Last Name  Birth Date                ORDER BY ROWID ROWS BETWEEN UNBOUNDED PRECEDING                                         AND UNBOUNDED FOLLOWING                     AS CNT               FROM                YourTable               WHERE                Load Date IS NULL       SELECT       RID      FROM       duplicates      WHERE       RN  gt  1     Somethings to note   1  We are only checking for duplication on the fields in the partition clause   2  If you have some reason to pick one duplicate over others you can use an order by clause to make that row will have row number     1  3  You can change the number duplicate preserved by changing  the final where clause to  Where RN   N   with N    1  I was thinking N   0 would delete all rows that have duplicates  but it would just delete all rows    4  Added the Sum partition field the CTE query which will tag each row with the number rows in the group   So to select rows with duplicates  including the first item use  WHERE cnt   1

User · Answer

Using rowid-  delete from emp  where rowid not in   select max rowid  from emp group by empno     Using self join-  delete from emp e1  where rowid not in   select max rowid  from emp e2  where e1 empno   e2 empno

[sql] Removing duplicate rows from table in Oracle

Examples related to sql

Examples related to oracle

Examples related to duplicates

Examples related to delete-row