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


Please Help Members By Posting Answers For Below Questions

What is openxml in sql server?

755


What is sparse columns of sql server 2008?

688


What are the types of subquery?

630


How to sort query output in descending order in ms sql server?

644


To which devices can a backup be created and where should these devices be located? : sql server management studio

641






What is the partitioning method?

642


What is database replication? What are the different types of replication you can set up in sql server?

587


What are the types of subscriptions in SQL Server replication?

627


What is the default Port No on which SQL Server listens?

715


What is the difference between dropping a database and taking a database offline?

784


What do you know about normalization and de- normalization?

541


What is stored procedures?

619


What is the stuff and how does it differ from the replace function?

628


What is optimization and its types?

617


difference between Clustered index and non clustered index ?

671