question 1

[txwylde]

My question is there is duplicate records that are in two tables. The first table is called Contacts. The unique Identifier is usage_indicator and name. I tried doing this:

Delect ABMcontacts
where usage_indicator not in
(select min (usage_indicator)
from ABMcontacts group by usage_indicator, name)
and name not in
(select min (name)
from ABMcontacts group by usage_indicator, name)


this runs for over an hour and does not do anything. Is there a way to pull duplicate records based on the usage_indicator and name and delete then?

Thanks!

 

[JNC73]

Hi txWylde,

First your query takes forever because for each record to check in the table ABMcontacts it will run the queries with the function MIN(..) FROM ... group by.

This will take forever. Secondly the two queries always produce the same output each time you run it. MIN(...) is always the same because the queries don't have other search criteria.

To speed up your proces execute the subqueries apart and only give the end result to the query you described.

Hope this helps

JNC73

 

[tlbroadbent]

The query will run a long time because for every record in the table both subqueries will run. For example, if the table has 10,000 rows, SQL Server will run each subquery 10,000 times.

You can identify the duplicates with this query.

Select usage_indicator, name, count(*) As cnt
From ABMcontacts
Group By usage_indicator, name
Having count(*) > 1

There is an excellent article on the SQLTeam website about deleting duplicates. It lists a few methods. You can choose the one that is best for you.

http://www.sqlteam.com/item.asp?ItemID=3331

Terry

http://members.home.net/tlbroadbent/index.htm