SQL Server Performance Forum – Threads Archive

Is there a magician on earth?

Hi all, Just a quick question, I’ve a big problem in my DB… I’ve a Table Customer with 60000 records, Paper and Transaction The Table Customer have a lot of duplicates. Explanation ID – FirstName – LastName – DOB – Etc.. 1 – Obelix – Asterix – O1.01.1900 – Etc.. 2 – Obelyx – Asterix – 26.03.1987 – Etc.. 3 – Asterix – Obelix – 01.01.1900 – Etc.. 4 – Obelix – Asterix – 03.26.1987 – Etc.. … The Table Paper : ID – IDCustomer – Type – Nb-Etc.. 1 – 1 – x – 123 -Etc.. 2 – 2 – y – 234 -Etc.. 3 – 2 – y’ – 567 -Etc.. 4 – 4 – z – 999 -Etc.. … The Table Transaction : ID – IDCustomer – Transaction-Etc… 1 – 2 – 400-Etc.. 2 – 2 – 100-Etc.. 3 – 1 – 100-Etc.. 4 – 3 – 400-Etc.. 5 – 4 – 200-Etc.. … Hope it’s clear…[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] I would like to transfer the transaction on 1 unique customer and delete the others Any idea? Any help would be welcome

Create a conversion table for the duplicate Customer IDs, where you list the IDs that you want to remove, with the ID that will replace the duplicate ID. – This is inevitably a manual process, though you can probably speed this up by running some queries to match typical variations-through-misspelling. Now update the customer ID on both Paper and Transaction for the duplicate IDs. Finally delete the duplicate IDs from Customer.

Thanks for that but there are more than 60000 customers and I can’t do that manualy…it’s an amazing job…I’ve search into the faq’s but I can’t find a similar issue. If someone could show me the way…

Well, the magician you’re looking for usually responds by the time the job is done, and he’ll say "You know what, I could have done that in less time." A few queries to help you under way: — 1 get all custumors with the same DOB
SELECT C.*, D.*
FROM Customer C
INNER JOIN Customer D
ON C.DOB = D.DOB
WHERE C.ID < D.ID — 2 the same, but filtered for matching LastName
SELECT C.*, D.*
FROM Customer C
INNER JOIN Customer D
ON C.DOB = D.DOB AND C.LastName = D.LastName
WHERE C.ID < D.ID … and continue with a mix-and-match of the detail columns in the ON clause. Mind you, if your sample data is anything to go by then it looks like your DOB column is not a true date field, so you have a huge problem. Will it be worth the effort? Garbage in, garbage out …

Hi Adriaan, You put one’s finger on the source of the trouble…garbage in… garbage out…
Sleep on it…I’ll try your advice and see if I can work with it Thanks

]]>