Finding Duplicate records

[liquid_One]

I have a customer table that is getting a duplicate customer problem...and I need a way to fix that....I made a script to help prevent it in the future, but I need a way to clean it out in the meantime....
Whats the best way to go about doing this in MSSQL2000 / CF7?
I would like to maybe just get a list of pks, ....then i could write a script to let me look at them, and decide which one to keep, and tranfer all the child references to the one im keeping for each customer. It has about 15,000 customers in all, and has all the typical customer fields, as well as a couple FK's....
I couldnt think of any real efficient way to do this, but figured some of you must have found better ways, and why reinvent the wheel....
Thanks,
Mike

[paross1]

SELECT t1.your_pk
FROM your_table t1
WHERE EXISTS(SELECT 1
FROM your_table t2
WHERE t1.your_pk = t2.your_pk
HAVING COUNT(*) > 1)

Phil

[cf_dev2]

Or if its the same table, just

SELECT ThePrimaryKey, count(*)
FROM YourTable
GROUP BY ThePrimaryKey
HAVING COUNT(*) > 1

[Dan_Bracuk]

quote:

---

Originally posted by: cf_dev2
Or if its the same table, just

SELECT ThePrimaryKey, count(*)
FROM YourTable
GROUP BY ThePrimaryKey
HAVING COUNT(*) > 1

---

If it's the primary key, how could this query ever return any results?

[paross1]

I'm sure that he is calling it a primary key, but of course, by definition, a PK has no duplicates or NULLs. So, I am assuming that his intent is to "clean up" the database so that he can eventually enable the proper constraints appropriate for a "real" primary key field.

Phil

[cf_dev2]

quote:

---

Originally posted by: Dan Bracuk
If it's the primary key, how could this query ever return any results?

---

Originally, that was my thought as well. But I suspect paross1 is correct and liquid One is cleaning up the data so he can properly implement the primary key / constraints.

[liquid_One · Accepted solution]

Perfect! thats exactly what I needed....
Thanks guys....