checking for duplicates and changing the records 2

[Nickman]

Hi

Table design is:

TCustomer
.Cust_ID
.Name
.Street
.Zip
.Code

What I've already got:

SELECT 
 TCustomer.Name, 
 TCustomer.Zip, 
 TCustomer.Cust_ID, 
 Count(TCustomer.Name) AS [Nr.of.Names], 
 Count(TCustomer.Zip) AS [Nr.of.Zips]
FROM 
 TCustomer
GROUP BY 
 TCustomer.Name, 
 TCustomer.Zip,
 TCustomer.Cust_ID
HAVING 
 (((Count(TCustomer.Name))>1) AND ((Count(TCustomer.Zip))>1));

The result is:

Name Zip Cust_ID Nr.of.Names Nr.of.Zip
Joe 88342 12345 4 4

The Problem I've got, is that I have to check if .Code is empty or not (and display the one with Infos) and if the .Street is empty INSERT the information from the duplicate.

The next step is to put all the first "Cust_ID" into an array, and the duplicates into another array. (This is so I can change other Tables which are linked by the "Cust_ID".

appreciate any help at all
Nick

 

[JoliM]

Nick,

Are you trying to do this from the database, or from an ASP page? Sounds like you need to do this in multiple steps. First select your information based on whether or not .Code is empty.

SELECT
TCustomer.Name,
TCustomer.Zip,
TCustomer.Cust_ID,
Count(TCustomer.Name) AS [Nr.of.Names],
Count(TCustomer.Zip) AS [Nr.of.Zips]
FROM
TCustomer
WHERE
Code is null or Code = ""
GROUP BY
TCustomer.Name,
TCustomer.Zip,
TCustomer.Cust_ID
HAVING
(((Count(TCustomer.Name))>1) AND ((Count(TCustomer.Zip))>1))

It wasn't clear what you were going to do with this data once you pulled it.

Then, do a table join against the same table to update the information for .Street.

UPDATE TCustomer
SET Street = tc.Street
from TCustomer t, Tcustomer tc
where t.Cust_ID = tc.Cust_id
and tc.Street is not null

Anytime before doing an update, select the data first to make sure it looks right. So convert this statement to a select statement first to check syntax:

SELECT
t.Street, tc.Street,t.Cust_id,tc.Cust_id
from TCustomer t, Tcustomer tc
where t.Cust_ID = tc.Cust_id
and tc.Street is not null

Hope that helps,
Joli

 

[Nickman]

thanks for the help Joli.

the second step will be to update (to the now correct Record) paralell tables which are linked with the Customer_ID and then delete all duplicates.

Do you think there will be problems making this seperate table, as there are over 1 mio. records?

 

[Nickman]

P.S.

I'll be doing this over a couple of asp-pages.
-update page
-controll page
-update page
-controll page
etc...

 

[JoliM]

I would not advise doing these updates using an ASP page. It would be better if you have direct access to the database because with over 1 million records, your session through the browser may time out and then you would have to find out what did not get updated, or where the query stopped.

To do your update you could create a table that contains all the correct IDs in one column and the old IDs that map to them in another column.

tblCustID
------------
Cust_ID Old_Cust_ID
1111 1224
1111 1225

Then create an update statement such as:

Update tblParallelTable
set Cust_ID = C.Cust_ID
from tblCustID C, tblParallelTable P
where Old_Cust_ID = P.Cust_ID

Joli