delete duplicate customer by first & lastname

Jan 18, 2008 6 Replies
D

I have a sql script below that deletes duplicate customers by LASTNAME only. If you could help fill in the correct syntax to allow duplicate customers deleted by FIRSTNAME and LASTNAME.



SQL lastname only script:



DELETE Customer FROM Customer INNER JOIN (SELECT MIN(ID) AS MinID, MAX(ID) AS MaxID FROM Customer WHERE Lastname IN (SELECT Lastname FROM Customer GROUP BY Lastname HAVING COUNT(Lastname) > 1) GROUP BY Lastname) a ON Customer.ID = a.MaxID WHERE Customer.ID = a.MaxID



Thanks for your help Dave


Vote
Twitter Facebook LinkedIn
L

If you delete customer records solely based upon either last name or first name you may be deleting information that you don't want to. Plus, I'd recommend combining records FIRST before you delete - this way you can retain purchase history.

But, that's just my > I have a sql script below that deletes duplicate customers by LASTNAME only.

Vote
Twitter Facebook LinkedIn
L

Then I would recommend that you COMBINE the duplicate customer records (use on as the master); update it and all of the corresponding transaction data; and then delete the other customer record or records. You really should do this in the HQ and the SO databases. If you need the SQL for doing so contact me offline at snipped-for-privacy@venus.com and I can send it to you.

Scott

"DCR" wrote:

Vote
Twitter Facebook LinkedIn
S

Check a script for merging duplicate records with sales history transfer at

formatting link
Please note that a similar script for HQ will be slightly different.

Vote
Twitter Facebook LinkedIn

Join the Discussion

Have something to add? Share your thoughts — no account required.

Didn't find your answer?

Ask the community — no account required