Delete AND preserve duplicates

[efron]

My brain hurts.

I have a client that process lists of names and addresses. He recieves them from various sources.

He needs to filter the lists as they come in to remove duplicate entries.

I merge the table that has his old data with the list that has the new data in a temporary table and then choose distinct from the temp table to filter out the duplicates using the apropriate columns. So far so good. Now I have the uniques.

So now, what's the easy way to find the duplicates and create a list (table) of those?

[Lars_Berg]

select name,address from t
group by name,address
having count(*) > 1

[michael]

The method I would use to delete and preserve duplicates is the enabled bool...

I'd use a tiny int (1) [bool] called enabled and use that to determine whether to show/ulilize the account. If it's a 0, it's
"deleted." Then you can purge deleted's from time to time. It's important to have a EPIC or better timestamp on these listings. When merging two, make the most current (the one with the latest date) ENABLED=1 and the ones that are old 0's.

Hope this helps with the original question.