Please give me queries for the following
1. To get the count of duplicate records.
2. A query to delete the duplicate records.
Answers were Sorted based on User's Feedback
Answer / 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 |
Answer / harshad lakkad (bapunagar part
For Count Duplicate---
SELECT YourColumn, COUNT(*) TotalCount
FROM YourTable
GROUP BY YourColumn
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
For delete Duplicate---
DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2,
DuplicateColumn2)
Is This Answer Correct ? | 4 Yes | 1 No |
Answer / meher
1. To get the duplicate records.
Use group by clause to the column on which you want to
check the duplicates records.
syntax:
select column_name, count(*) from table_name group by
column_name having count(*) >1
2. To delete the duplicate records.
syntax:
delete from table_name where rowid in (select max(rowid)
from table_name group by column_name having count(*)>1 )
This will work.
Please let me know in case of any issues.
Is This Answer Correct ? | 2 Yes | 2 No |
What is filestream?
What's the difference between a primary key and a unique key?
What are the type of joins? When do we use Outer and Self joins?
What is the advantage of sql server?
what exactly sql injuction.how to overcome.....
Why I have to use stored procedures?
Can sql servers link to other servers?
Can we insert data into view sql server?
What is database isolation in sql server? : sql server database administration
What is the purpose of self join?
How do I edit a stored procedure in sql server?
What is the default server name for sql server?