Please give me queries for the following
1. To get the count of duplicate records.
2. A query to delete the duplicate records.
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 is openxml in sql server?
What is sparse columns of sql server 2008?
What are the types of subquery?
How to sort query output in descending order in ms sql server?
To which devices can a backup be created and where should these devices be located? : sql server management studio
What is the partitioning method?
What is database replication? What are the different types of replication you can set up in sql server?
What are the types of subscriptions in SQL Server replication?
What is the default Port No on which SQL Server listens?
What is the difference between dropping a database and taking a database offline?
What do you know about normalization and de- normalization?
What is stored procedures?
What is the stuff and how does it differ from the replace function?
What is optimization and its types?
difference between Clustered index and non clustered index ?