HOW TO DELETE DUPLICATE RECORD WITHOUT PRIMARY KEY [Resolved]

Posted by Rahmankhan under ASP.NET on 8/20/2013 | Points: 10 | Views : 2084 | Status : [Member] | Replies : 8

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

MY TABLE LIKE THIS (1001 IS DUBLICATE RECORD )

1001 rahmankhan mca
1002 malik mba
1003 abdul wahab bsc
1004 sinnaraja bcom
1005 guthus mcom
1001 rahmankhan mca

MY QUERY :

//* find the dublicate record *//

select max(rollni)as rollno,name,qualification from record
group by name, qualification having count(*)>1

//* delete the dublicate record *//

delete from record where rollni in(select max(rollni) from record
group by rollni ,name ,qualification having count(*)>1 )

RESULT :

HERE I GET RESULT IS 1001 DELETED .

BUT I NEED 1001 RECORD ONE TIME

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 8/20/2013 [Member] [MVP] Platinum | Points: 50

0	-- To delete duplicate record based on rollni column ;WITH CTE AS ( SELECT ROW_NUMBER() OVER(PARTITION BY rollni ORDER BY name) RN from record ) DELETE FROM CTE WHERE RN >1 -- Alternate is DELETE Temp FROM (select ROW_NUMBER() OVER(PARTITION BY rollni ORDER BY name) RN from record ) AS Temp WHERE RN >1 NOTE: if there is no primary key for the record and you wish to delete record which has same values as another record, then you should do as follows DELETE Temp FROM (select ROW_NUMBER() OVER(PARTITION BY [b]rollni,name,qualification[/b] ORDER BY name) RN from record ) AS Temp WHERE RN >1 Place list of all columns in the PARTITION BY clause of ROW_NUMBER() Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Rahmankhan, if this helps please login to Mark As Answer. \| Alert Moderator

-- To delete duplicate record based on rollni column

;WITH CTE AS 

( SELECT ROW_NUMBER() OVER(PARTITION BY rollni ORDER BY name) RN from record 

) 

DELETE FROM CTE WHERE RN >1

-- Alternate is

DELETE Temp 

FROM (select ROW_NUMBER() OVER(PARTITION BY rollni ORDER BY name) RN from record ) AS Temp 

WHERE RN >1

NOTE: if there is no primary key for the record and you wish to delete record which has same values as another record, then you should do as follows

DELETE Temp 

FROM (select ROW_NUMBER() OVER(PARTITION BY [b]rollni,name,qualification[/b] ORDER BY name) RN from record ) AS Temp 

WHERE RN >1

Place list of all columns in the PARTITION BY clause of ROW_NUMBER()

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Rahmankhan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/27/2013 [Member] [MVP] Platinum | Points: 50

0	@Rahmankhan Can you mark it as answer.... If you haven't solved your issue let us know so that we can help you Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Rahmankhan, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Allemahesh on: 8/20/2013 [Member] [MVP] Silver | Points: 25

0	Another way using table variable is as follows:- declare @temp as table ( rollni int, name varchar(50), qualification varchar(50) ) insert into @temp select distinct * from [dbo].[record] delete from [dbo].[record] insert into [dbo].[record] select * from @temp Happy coding. Rahmankhan, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Rahmankhan on: 8/20/2013 [Member] Starter | Points: 25

0	thnks for your valuable infermation bandi i got result Rahmankhan, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 8/20/2013 [Member] [MVP] Platinum | Points: 25

0	Mark reply Posted on: 8/20/2013 4:44:36 AM as answered.... Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Rahmankhan, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Varun66 on: 8/22/2013 [Member] Starter | Points: 25

0	Hi, You can go with the following query delete from emp where ('Delete',sal) in ( select case when count()>1 then 'Delete' else 'No' end DeleteFlag ,sal from emp group by sal having count()>1) Mark as answer if satisfied........ Regards, Shree M. Rahmankhan, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 8/26/2013 [Member] [MVP] Platinum | Points: 25

0	@Varun, You solution will delete only duplicate salary rows... Whereas Ramankhan's requirement is to delete duplicate row which is exactly same as any other row... @Ramankhan Can you mark it as answer if you fixed your issue.... Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Rahmankhan, if this helps please login to Mark As Answer. \| Alert Moderator

@Varun,
You solution will delete only duplicate salary rows... Whereas Ramankhan's requirement is to delete duplicate row which is exactly same as any other row...

@Ramankhan
Can you mark it as answer if you fixed your issue....

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Rahmankhan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rahmankhan on: 8/30/2013 [Member] Starter | Points: 25

0	thnks Bandi Rahmankhan, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts