Write a query to delete duplicate records in SQL SERVER
Answers were Sorted based on User's Feedback
Answer / dharmesh
your table look like this and want to delete duplicate
record
chandran 23
ranjith 24
chandran 23
delete top(1) from tablename where name='chandran' and
age=23
Is This Answer Correct ? | 3 Yes | 28 No |
Create table info(comp_id int identity(101,1),comp_name
varchar(50))
insert into info values('Progressive Ltd.')
insert into info values('Progressive Ltd.')
insert into info values('EliResearch')
insert into info values('Patni')
insert into info values('Accenture')
insert into info values('Accenture')
select * from info
DELETE FROM info
WHERE comp_name IN
(SELECT comp_name FROM info
GROUP BY comp_name HAVING COUNT(comp_name) > 1)
Is This Answer Correct ? | 12 Yes | 38 No |
Hi friends, please just try out this. This works fine for me.
We have lot of methods to do this. But using temp table,
drop the original table,retain the temp as orinial is not a
good pratice.
When u have large no of data it will affect ur performance.
DELETE FROM employee WHERE((SELECT eid,COUNT(eid) FROM
employee GROUP BY eid) > 1)
Is This Answer Correct ? | 15 Yes | 46 No |
Answer / chandran.s
Table Name: Example
Name Age
chandran 23
ranjith 24
chandran 23
To delete one of the duplicate records use following query
delete from example where age in(select age from example
group by age having count>1)
Is This Answer Correct ? | 13 Yes | 46 No |
Answer / chandran
There is a table like this: tablename: example
Name Age
chandran 23
ranjith 24
chandran 23
In this table the name:chandran and age:23 are the
duplicate records .so we need to delete this using this
sql statements
delete from example group by name,age having count>1
Is This Answer Correct ? | 37 Yes | 107 No |
How to turn off warning messages during php execution?
How to write an inner join with the where clause in ms sql server?
What do you understand by hotfixes and patches in sql server?
How to replace null values in expressions using isnull()?
Types of Authentications in Sql Server? How user gets authenticated through windows authentication?
What are examples of triggers?
What is the largest component inside a field?
Explain what are sparse columns?
Write a stored procedure for emplpoyee and department table to get DeptName which having no employee. Table Structure- Emp-Emp_Id,Emp_Name,Dept_id Dept-Dept_Id,Dept_Name
Define a cross join?
Can we use trigger new in before insert?
What is a collation in ms sql server?