CODEWITHSUNDEEP
sqloracle-database
Iftekhar
Iftekhar

Reputation: 365

How to delete duplicate rows in Oracle

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

Upvotes: 3

Views: 17585

Answers (4)

Pradeep Wagh
Pradeep Wagh

Reputation: 19

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

Upvotes: 0

sqluser
sqluser

Reputation: 5672

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
   )

Upvotes: 4

user3278460
user3278460

Reputation:

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 
)

Upvotes: 1

Moudiz
Moudiz

Reputation: 7377

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)

Upvotes: 2

Related Questions