How to delete duplicate rows in Oracle

Question

I need help with duplicate rows. I have deleted the duplicate rows from one table using the following method

DELETE FROM names a
WHERE ROWID > (SELECT MIN(ROWID) FROM names b
WHERE b.name=a.name
AND b.age=a.age
);

It did work with that particular table but I did the same thing with another table which has duplicate reference numbers for each doctor but different unique codes.

doc_name  ref_no  unique_code
------------------------------
abcd      1010     1111
abcd      1010     1112
cdef      1011     1113
cdef      1011     1114

My result should look like this:

doc_name ref_no unique_code
---------------------------
abcd     1010      1111
cdef     1011      1113

what is the query you used for the 2nd query?

Moudiz
– Moudiz

2015-05-31 19:16:57 +00:00
Commented May 31, 2015 at 19:16 — Moudiz
– Moudiz, Commented May 31, 2015 at 19:16

sqluser · Accepted Answer · 2015-06-01 11:07:08Z

4

You can use ROW_NUMBER(), to detect duplicate rows and delete them.

DELETE tblName
WHERE ROWID IN (
    SELECT ROWID
    FROM(
      SELECT ROW_NUMBER() OVER (PARTITION BY doc_name, ref_no ORDER BY doc_name, ref_no) AS Rn
            ,doc_name
            ,ref_no
            ,unique_code
      FROM tblName
    )
    WHERE Rn > 1
   )

edited Jun 1, 2015 at 11:07

answered Jun 1, 2015 at 6:18

sqluser

5,7127 gold badges44 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user330315 Over a year ago

You can't delete from a CTE in Oracle

Iftekhar Over a year ago

@sqluser i tried your query.it showed me the following error:

WITH C AS(            SELECT ROW_NUMBER() OVER (PARTITION BY doc_name, ref_no     ORDER BY doc_name, ref_no) AS Rn ,  doc_name ,  ref_no     ,  unq_cd FROM doc_unique )   DELETE FROM C WHERE Rn > 1

SQL> / DELETE FROM C WHERE Rn > 1 * ERROR at line 8: ORA-00928: missing SELECT keyword

sqluser Over a year ago

@Iftekhar, as @a_horse_with_no_name mentioned in his comment we cannot delete from a CTE. So I changed the query. Please see the updated. Thanks to @a_horse_with_no_name for pointing it out.

Iftekhar Over a year ago

@sqluser,thanks for the updated query.i have changed the new query a little as there is no ref_no in the table where i am looking to delete the duplicate rows.will the new query work or should i include father's name(ft_nm) and mother's name(mt_nm) too?

DELETE t_doc_unique WHERE ROWID IN (     SELECT ROWID     FROM(       SELECT ROW_NUMBER() OVER (PARTITION BY doc_name,unq_cd ORDER BY doc_name, unq_cd) AS Rn             ,doc_name             ,unq_cd       FROM t_doc_unique     )     WHERE Rn > 1    ) /

Moudiz · Accepted Answer · 2015-06-01 06:04:20Z

2

Did you try it like this ?

DELETE FROM names a
WHERE ROWID > (SELECT MIN(ROWID) FROM names b
WHERE b.doc_name=a.doc_name
AND b.ref_no=a.ref_no
)

try this also

SELECT *
  FROM doc_unique
 WHERE (DIV_CD, DOC_NAME, B_DT, FT_NM, UNQ_CD, DESG_CD,
 SPEC_CD) IN (SELECT DIV_CD, DOC_NAME, B_DT, FT_NM, UNQ_CD, DESG_CD,
 SPEC_CD
                             FROM doc_unique
                            GROUP BY DIV_CD, DOC_NAME, B_DT, FT_NM, UNQ_CD, DESG_CD,
 SPEC_CD HAVING COUNT(*) > 1)

edited Jun 1, 2015 at 6:04

answered May 31, 2015 at 19:18

Moudiz

7,42723 gold badges87 silver badges166 bronze badges

3 Comments

Iftekhar Over a year ago

Moudiz,i did a manual search on the table and also checked for duplicate values but there was none could be found. can you tell me if this is the proper way to check for duplicate rows

select DIV_CD,DOC_NAME,B_DT,FT_NM,UNQ_CD, DESG_CD,SPEC_CD,count(*) from doc_unique  group by DIV_CD,DOC_NAME,B_DT,FT_NM, UNQ_CD,DESG_CD,SPEC_CD  having count(unq_cd)>1

Moudiz Over a year ago

@Iftekhar yes it seems good however check this query , its another way to check duplicate

Iftekhar Over a year ago

your query to check for duplicates worked.thanks a lot

user3278460 · Accepted Answer · 2015-06-01 06:15:38Z

1

Please, try exists

delete from names a
where exists (
  select * 
  from names b
  where b.name = a.name
        and b.age = a.age
        and a.unique_code > b.unique_code 
)

answered Jun 1, 2015 at 6:15

user3278460

Comments

Pradeep Wagh · Accepted Answer · 2022-11-14 14:17:43Z

0

you can delete duplicate data by using following sql query:

delete from [table_name] where rowid in ( select max(rowid) from [table_name] group by [column_name] );

Note: [table_name] like STUDENT and [column_name] like STUD_ID

Find Screenshot

edited Nov 14, 2022 at 14:17

answered Nov 14, 2022 at 14:03

Pradeep Wagh

193 bronze badges

2 Comments

atish.s Over a year ago

Avoid sharing screenshots, link might get broken throughout time

Pradeep Wagh Over a year ago

Thank you for sharing, I will take care from now @atish.s

Collectives™ on Stack Overflow

How to delete duplicate rows in Oracle

4 Answers 4

4 Comments

3 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related