Deleting duplicate rows from the table

kvramanareddy · New User Joined: 24 Mar 2005 Posts: 9

Hi,

How to delete the duplicate rows from the table by maintaining one copy(Suppose there are 3 duplicate rows and i want to delete 2 rows from the table i need to maintain ).

Please let me know.

Thanks & Regards,
K.V.Ramana Reddy.

sivatechdrive · Posted: Fri Apr 22, 2005 2:13 pm

Hi Friend

Write a select Cursor query & issue a delete

Or i think this query may help

DELETE field1 FROM EMP A
group by field1
Having count(field1) > 1

Regards
siva

jhaam · New User Joined: 22 Apr 2005 Posts: 3

Hey Shiva
what is that DELETION !!!

first thing
DELETE filed1... we can't delete by fields...

second thing...

here it will delete all the rows , but he asked to delete only two rows among three rows.

jhaam · New User Joined: 22 Apr 2005 Posts: 3

Reddy,
I think we can delete the duplicate records in one parse.

Code:

DELETE
FROM TABLE A
WHERE (A.COL1,A.COL2,A.COL3)
IN (SELECT B.COL1,B.COL2,B.COL3
FROM TABLE B
WHERE A.COL1 = B.COL1
AND A.COL2 = B.COL2
AND A.COL3 = B.COL3
GROUP BY B.COL1, B.COL2, B.COL3
HAVING COUNT(*) > 1);

The above code could answer ur question... I have taken this answer from the previopus posts only...

kvramanareddy · New User Joined: 24 Mar 2005 Posts: 9

Hi Jhaam,

Thank You very much for your good reply.

Thanks & Regards,
K.V.Ramana Reddy.

vijayamadhuri · Active User Joined: 06 Apr 2005 Posts: 180

Please correct me if I am wrong but we can use the DISTINCT keyword to remove duplicates.

Girishm · New User Joined: 09 Mar 2005 Posts: 35 Location: Mysore

You are absolutely wrong Madhuri, the original question was to delete the duplicates from the table itself but DISTINCT keyword will not delete the duplicates from the table, instead it just avoids displaying duplicate rows for the select query.
Hope you got the clear picture now....

___________
GM

vijayamadhuri · Active User Joined: 06 Apr 2005 Posts: 180

Thanks a lot for the clarification.

harikgudipati · New User Joined: 13 May 2005 Posts: 1

Hi all,

I don't think the above query given by JHAAM will work. Because above query deletes all the rows not only duplicates.

Jhaam: Can you justify how it will work