Write a query to delete duplicate records in SQL SERVER

Categories >> Software >> Databases >> SQL Server
Suggest New Category

Question Posted / durga prasad

36 Answers
67556 Views
Infosys, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

Write a query to delete duplicate records in SQL SERVER..

Answer / saurabh dixit

DECLARE @people TABLE
(
name VARCHAR(32),
age INT
)
insert into @people
select Name,age From People group by Name,Age having COUNT(name)>1

delete People from People pe
join @people p on pe.name=p.name and pe.age=p.age

Is This Answer Correct ?

0 Yes

1 No

Write a query to delete duplicate records in SQL SERVER..

Answer / prakash patel

delete from tbl_test where ID in
(select ID from tbl_test group by ID having count(ID) > 1)

Is This Answer Correct ?

0 Yes

1 No

Write a query to delete duplicate records in SQL SERVER..

Answer / vaishali

set rowcount 1
delete from stud
where (select count(*) from stud a where stud.roll_no =
a.roll_no) > 1
while @@rowcount<>0
delete from stud
where (select count(*) from stud a where stud.roll_no =
a.roll_no) > 1
set rowcount 0

Is This Answer Correct ?

3 Yes

6 No

Write a query to delete duplicate records in SQL SERVER..

Answer / shashank shrivastava

Table-admin
------------------
adminID adminName
1 admin
2 tester
3 admin

SQL:
---------------
DELETE admin.* FROM admin,
(SELECT count(adminName) as c, MAX(adminID) as m from
admin GROUP BY adminName HAVING c>1) as new_admin
WHERE admin.adminID=new_admin.m

Is This Answer Correct ?

1 Yes

5 No

Write a query to delete duplicate records in SQL SERVER..

Answer / senthilsjc

This query delete duplicate records(but not delete both
duplicate)
set rowcount 1
delete yourtable
from yourtable a
where (select count(*) from yourtable b where b.name=a.name
and b.age=a.age)>1
while @@rowcount >0
delete yourtable
from yourtable a
where(select count(*) from yourtable b b.name=a.name and
b.age=a.age)>1
set rowcount 0

Is This Answer Correct ?

6 Yes

12 No

Write a query to delete duplicate records in SQL SERVER..

Answer / subrata paul

Delete TOP(1)from example where[ID]=1

Is This Answer Correct ?

4 Yes

10 No

Write a query to delete duplicate records in SQL SERVER..

Answer / ashish kumar

select * from dbo.duplicatetest
set rowCount 1
delete from dbo.duplicatetest where iD=1;
set rowCount 0

Is This Answer Correct ?

3 Yes

10 No

Write a query to delete duplicate records in SQL SERVER..

Answer / rajkamal

delete form emp
where (select distinct(ename)from emp)

Is This Answer Correct ?

3 Yes

11 No

Write a query to delete duplicate records in SQL SERVER..

Answer / vinod singh kushwah

Delete From Tablename where(ID Not in (Select max(ID) from
Tablename Group by name))

Tablename :Friend

ID Name Age city
101 vinod 22 Gwalior
102 Pritesh 23 Gwalior
102 Pritesh 23 Gwalior
103 Arvind 24 Gwalior

Here Id-102 is repeated so friend if u want to delete this
duplicate raw Try Above code in Sql-sever

Is This Answer Correct ?

4 Yes

13 No

Write a query to delete duplicate records in SQL SERVER..

Answer / anuj

You have Two option You can use keyword Distinct or Group By Both will help you to delete dublicate records

Is This Answer Correct ?

1 Yes

11 No

Post New Answer