Finding duplicates in a table

Last Post 07 Oct 2007 10:17 AM by SwePeso. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
stabos
New Member
New Member

--
03 Oct 2007 07:07 AM


I have a table that has the following fields:

Cust_ID, Name, Address1, Zip

I need to find duplicated records that have the same Name Address1 and Zip and dump them into another table that has the same fields. These records can have different Cust_ID's which was caused by users adding the same information into this table multiple times causing records with identical name/address info to have different Cust_ids. I need to eliminate this by having only 1 record per customer.

How do I do this?

Any info will help

Thank You.

SwePeso
New Member
New Member

--
03 Oct 2007 08:33 AM
DELETE x
OUTPUT deleted.Cust_ID,
deleted.Name,
deleted.Adderss1,
deleted.Zip
INTO Table2
FROM Table1 AS x
INNER JOIN (
SELECT Cust_ID,
ROW_NUMBER() OVER (PARTITION BY Name, Address1, Zip ORDER BY Cust_ID DESC) AS RecID
FROM Table1
) AS d ON d.Cust_ID = x.CustID
WHERE d.RecID > 1

SwePeso
New Member
New Member

--
03 Oct 2007 08:34 AM
stabos
New Member
New Member

--
04 Oct 2007 07:45 AM
Thank You Peter it seemed to have worked. I do have one question.

I do not want to delete the records out of the main table. How would I do that?
SwePeso
New Member
New Member

--
04 Oct 2007 11:50 AM
Well... You wrote "eliminate" in your original post.
stabos
New Member
New Member

--
05 Oct 2007 06:28 AM
Yes I did. I also wrote in the begining to identify dups and dump them into another table.

I tried to replace the delete with select per rm and it didn't work.

What am I doing wrong?

Thanks
SwePeso
New Member
New Member

--
07 Oct 2007 10:17 AM
My code will do all that.
Just make sure Table2 is present.
You are not authorized to post a reply.

Acceptable Use Policy