Interested to Buy Any Domain ? << Click Here >> for more details...
Please give me queries for the following
1. To get the count of duplicate records.
2. A query to delete the duplicate records.
- 3 Answers
- 6817 Views
- 247Customer, I also Faced
- E-Mail Answers
Answer Posted / srikanth
1. use group by clause with the field on which you want to
check for duplicates
Ex: select salary,count (*) from employee group by salary
having count (*)>1
2. Use the below query to delete the duplicate records (not
the original ones)
SET ROWCOUNT 1
DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 =
a.name1 AND b.age1 = a.age1) > 1
WHILE @@rowcount > 0
DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 =
a.name1 AND b.age1 = a.age1) > 1
SET ROWCOUNT 0
| Is This Answer Correct ? | 5 Yes | 0 No |
Post New Answer View All Answers
What does the on update no action do?
What is the primary use of the model database?
What is the new security features added in sql server 2014? : sql server security
Can we perform backup restore operation on tempdb?
What is the difference between varchar and nvarchar datatypes?
What is a View ? Can we insert, Update and delete a view?
What are the five characteristics of good data?
How you can move data or databases between servers and databases in sql server?
You are designing a database for your human resources department in the employee table, there is a field for social security number, which cannot contain null values if no value is given, you want a value of unknown to be inserted in this field what is the best approach?
How real and float literal values are rounded?
what’s the difference between Covering Indexes and Clustered Indexes ? how to use clustered index small ?
How can delete duplicate records in cte in sql server?
What is update locks?
Does a server store data?
What is the purpose of object explorer and its features? : sql server management studio
