Write a query to delete duplicate records in SQL SERVER
Answer Posted / ben mccameron
I have a query that I use with success I have a table with
telephone numbers in it and sometimes I have duplicate
phone numbers in the table... here is how I see what they
are and then remove them.
===========================================
select telephone
from Table_A
group by telephone having count(*) > 1
SET ROWCOUNT 1
SELECT NULL
WHILE @@rowcount > 0
DELETE step
FROM Table_A as step
INNER JOIN
(SELECT telephone
FROM Table_A
GROUP BY telephone HAVING count(*) > 1)
AS t ON t.telephone = step.telephone
SET ROWCOUNT 0
===========================================
This query will actually find and remove the duplicates
from Table_A but will not remove both instances it will
only remove one... leaving you with one good record... hope
this helps someone. : )
Is This Answer Correct ? | 11 Yes | 3 No |
Post New Answer View All Answers
Explain the steps to use transact-sql cursor?
Do you know what is openxml in sql server?
which backup strategy you are following at ur company
Difference between report and query parameter. Why do we need different type of parameter?
How to download and install microsoft .net framework version 2.0?
When you use @@error and try-catch?
What are the key configuration files for sql server reporting services ?
Can we create clustered index on composite key?
what are user defined datatypes and when you should go for them? : Sql server database administration
Explain the flow of creating a cube? : sql server analysis services, ssas
How to defragment indexes with alter index ... Reorganize?
What is master database? : SQL Server Architecture
How display code or Text of Stored Procedure using Sql query in Sql Server ?
What are the hotfixes and patches in sql server?
what is the different types of backups available in sql server? : Sql server database administration