Write a query to delete duplicate records in SQL SERVER
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
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 |
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 |
Answer / rajkamal
delete form emp
where (select distinct(ename)from emp)
Is This Answer Correct ? | 3 Yes | 11 No |
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 |
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 |
How can I tell if sql server is 32 or 64 bit?
How do I debug a stored procedure in sql server?
What is a fill factor?
Please explain that what are the basic functions for master, msdb, model, tempdb and resource databases? : SQL Server Architecture
What keyword you will use to get schema appended to the result set of a ‘for xml’ query?
What is the default sql server instance name?
What are the different types of triggers in SQL SERVER?
What is change tracking in sql server?
What is the full meaning of dml?
How self join is different from outer join?
How to configure odbc dsn with different port numbers?
What are page splits?