HOW TO DELETE DUPLICATE RECORD WITHOUT PRIMARY KEY [Resolved]

Posted by Rahmankhan under ASP.NET on 8/20/2013 | Points: 10 | Views : 1582 | Status : [Member] | Replies : 8
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




Responses

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

Up
0
Down

Resolved
-- 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

Up
0
Down

Resolved
@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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
@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

Up
0
Down
thnks Bandi


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

Login to post response