Delete duplicate rows with no primary key

Posted by Muhsinathk under Sql Server on 9/18/2012 | Points: 10 | Views : 3093 | Status : [Member] | Replies : 15
Hi,
How to delete duplicate rows with no primary key on sql server table?




Responses

Posted by: Hariinakoti on: 9/18/2012 [Member] Starter | Points: 25

Up
0
Down
See this example
create table MaxNthSalary1
( EmpId int not null, EmpName varchar(20) null,Salary money null, CreateDate datetime null,)

insert into MaxNthSalary1 values(1,'pramod',10,getdate())
insert into MaxNthSalary1 values(2,'rakesh',120,getdate())
insert into MaxNthSalary1 values(3,'Jindal',130,getdate())
insert into MaxNthSalary1 values(4,'Manoj',150,getdate())

insert into MaxNthSalary1 values(5,'sanjay',120,getdate())
insert into MaxNthSalary1 values(6,'ravi',150,getdate())
insert into MaxNthSalary1 values(7,'mayank',170,getdate())
insert into MaxNthSalary1 values(8,'pratap',170,getdate())

-- Find duplicate records
insert into MaxNthSalary1 values(5,'sanjay',125,getdate())
insert into MaxNthSalary1 values(6,'ravi',155,getdate())
insert into MaxNthSalary1 values(7,'mayank',175,getdate())
insert into MaxNthSalary1 values(8,'pratap',175,getdate())

select * from MaxNthSalary1 order By EmpName
--Check for dup data.
SELECT EmpId,EmpName FROM MaxNthSalary1
GROUP BY EmpId,EmpName
HAVING COUNT(*) > 1

Thanks & Regards
Hari

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

Posted by: Hariinakoti on: 9/18/2012 [Member] Starter | Points: 25

Up
0
Down
If u satisfy with my answer please click on Mark As Answer

Thanks & Regards
Hari

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

Posted by: Pgayath on: 9/18/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,

We can do it two ways.
1. Using SET ROWCOUNT <Required No> ON
2. Using the TOP keyword as below.

For example:
One way

SELECT * FROM dbo.duplicateTable 


SET ROWCOUNT 1
DELETE FROM dbo.duplicateTable WHERE ID = 1
SET ROWCOUNT 0

SELECT * FROM dbo.duplicateTable


Alternate Way
SELECT * FROM dbo.duplicateTable

DELETE TOP(1) FROM dbo.duplicateTable WHERE ID = 1
SELECT * FROM dbo.duplicateTable

Hope this explains you better.

Please let me know if you need further explanation.

Thanks and Regards,
Gayathri P

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

Posted by: Saratvaddilli on: 9/18/2012 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Create a temporary table which stores the distinct records of the original table and then rename it...... 


Thanks and Regards
V.SaratChand
Show difficulties that how difficult you are

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

Posted by: Muhsinathk on: 9/18/2012 [Member] Bronze | Points: 25

Up
0
Down
Thank you to all..

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

Posted by: Hariinakoti on: 9/18/2012 [Member] Starter | Points: 25

Up
0
Down
Please Mark As Answer if u satisfy with my answer.

Thanks & Regards
Hari

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

Posted by: Muhsinathk on: 9/18/2012 [Member] Bronze | Points: 25

Up
0
Down
@Pgayath Good Explanation..

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

Posted by: Muhsinathk on: 9/18/2012 [Member] Bronze | Points: 25

Up
0
Down
@Harii,I want delete query..but it is select query

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

Posted by: Muhsinathk on: 9/18/2012 [Member] Bronze | Points: 25

Up
0
Down
@Pgayath,
If there are huge records,we cant apply top 1 for deleteing duplicate data

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

Posted by: Vasanthmvp on: 9/18/2012 [Member] Starter | Points: 25

Up
0
Down
Hi Muhsinathk,
here is a simple query.. SQL Server maintains the physical location of the row rather rowid as in oracle.

Query:

delete from DuplicateRowsDlt where (%%physloc%%) not in (select MIN(%%physloc%%) from DuplicateRowsDlt group by FirstName)

Explanation:
subquery: First group by the column based on which you want to delete the rows. Min() gets the firstrow of the group.
Outerquery: deletes all the rows except the min row (specified with not in).

Regards.

Awesome Coding !! :)

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

Posted by: Muhsinathk on: 9/18/2012 [Member] Bronze | Points: 25

Up
0
Down
Thank you Vasanth

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

Posted by: Vasanthmvp on: 9/18/2012 [Member] Starter | Points: 25

Up
0
Down
If the issue is resolved, plz mark it as answer. :)

Awesome Coding !! :)

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

Posted by: Hariinakoti on: 9/18/2012 [Member] Starter | Points: 25

Up
0
Down
Good work Vasanth

Thanks & Regards
Hari

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

Posted by: Jayakumars on: 9/18/2012 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi
Muhsinathk
try this using Row_number put your delete query


SELECT ROW_NUMBER() OVER(ORDER BY DepartmentID DESC) AS Row,LastName
from dbo.employee


Mark as Answer if its helpful to you

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

Posted by: Sriramnandha on: 5/11/2013 [Member] Starter | Points: 25

Up
0
Down
DELETE TOP(1) FROM dbo.duplicateTable WHERE ID = 1


DELETE * FROM EMPLOYEE WHERE EMPID NOT IN (SELECT EMPID FROM EMPLOYEE GROUP BY EMPID HAVING COUNT(*)<1)



HOPE THIS WILL HELP................

sriram

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

Login to post response