Hi,

I have to run through a list of tables and check for duplicates. The tables have no primary key setup but we expect that there should be only 1 record for a combination of fields.

So for example if I keep it simple and imagine there is a table called customers with loads of fields and in here the uniqueness of each row is defined by the fields salesrepid and customerid so there should be only 1 occurance of eg salesrep xyz123 and customer abc123

What I need to do is somehow look at the table and check to make sure that the above combination of salesrep xyz123 and customer abc123 only occurs once and if it does then note it / report on it.

Any ideas on how to approach it? I imagine I could have a static table containing the tables to be checked plus the fields of each table that make up the uniquness. So if the tables for checking where in a table called checkthese then in there I could have a field called tablename, and then further rows called pkf1 (primary key field 1), pkf2, pkf3 and so on and for the customers table the pkf1 would have value salesrepid and pkf2 would have value of customerid.

I've probaly not made much sense up there but basically what I need to do is read a table that contains tablenames and fields that make up the uniqness of the table and then go and check those tables to make sure that there are no more than one of each record that makes up the uniqness.

any ideas?

tia,

Ruairi
Derry
Ireland

To remove the duplicate values in a particular field of a table , u can do the select distinct <field name > into <new table> .......option.To find out the duplicate values, I think, u can join the table to itself(Self join)

SELECT salesrep, customer
FROM customers
group by salesrep, customer
having count(*) > 1

Or you could cursor through and delete the dupes. This way you don't need to create another table and you could run this while people were using the table.