Comparing Two Arrays and Merging Values That Are a Near Match

[mkc]

Hi guys,

 

I wonder if you can offer any advice?

 

I have two user database tables that are about 70% identical. The problem is that with one of them, the phone number for each user has been cut short by two digits (it was an error on my part early on in the database design). 

 

What I need to do is to get the values from both tables and put them into separate arrays then compare the arrays in a loop. If, say, the first five digits of the phone number in each row of the first table match one entry in the second table then update the second table with the phone number (the full length one).

 

I'm about puzzled about how to approach this, I've still got a lot to learn about array functions, it seems like it should be fairly simple but I'm a bit lost as to where to begin.

 

Any advice would be massively appreciated!

 

 

[Psycho]

Just to be sure we aren't taking the long road, I have a question. Why not just update the phone numbers without the comparison. Now, I *think* what may be the issue is that you have the original DB and the current DB - and that the current DB has since been modified: phone numbers added, edited, deleted. So, you may just be wanting to correct those phone numbers which were copied from the original DB and cut off. Can you confirm if that is the case or what the situation is? That might help to create an easier solution.

 

Second, we might not have to query the two databases, dump the results into arrays, process the arrays, and then update the database. This might be able to be handled direction through a query or two. Please provide details about the two tables along with the relevant fields. For example, what fields are used to determine that it is the same record, what is the format of the phone number data, etc.

[mkc]

Thanks for the reply. 

 

You're correct, it is just the phone numbers that need correcting as they are missing the last two digits.

Yes, the current DB has since been modified with additional users added/deleted and phone numbers changed etc. which is unfortunate as it would have been quite straight forward if I'd have caught it earlier, it's too big for a manual job now.

 

So we have two tables, clients and clients-old, frustratingly they do not have the same primary key for reasons beyond my control. The only unique match would be firstname and surname. The phone number column is just called phone. This naming schema applies to both tables.

[Psycho]

And can you provide the details of how the phone numbers are stored? What are the field types? Are they stored with formatting (e.g. (123) 456-7890), etc. Exactly how many characters are the ones that are cut off?

[mkc]

Sorry that would have been helpful!

 

They are stored as VARCHARs. There's no formatting or spaces, a string of digits. It's exactly two characters missing, the first 9 are fine.

[Psycho]

Try the following SELECT query to see if it is pulling the records you think should be updated.

 

The query first JOINs the records between the two tables where both the firstname and surname are the same. Then, the WHERE clause keeps those where the value of the current phone is matches the first part of the original phone. Plus, the length of the current phone must be less than 10 characters.

SELECT c.firstname, c.surname, c.phone AS current_phone, o.phone AS original_phone
 
FROM clients c
JOIN clients-old o
  ON c.firstname = o.firstname
 AND c.surname   = o.surname
 
WHERE LEFT(o.phone, LENGTH(c.phone)) = c.phone
  AND LENGTH(c.phone) < 10

Alternatively, you can change the WHERE clause to this

WHERE LEFT(o.phone, LENGTH(c.phone)) = c.phone
  AND LENGTH(c.phone) < LENGTH(o.phone)

In this case the value of the current phone must match the beginning of the original phone and it must have less characters.

 

 

Without seeing the data, I can't say what the optimal method is for getting the right records. Once we have the appropriate SELECT query, we can modify it to do an UPDATE instead.

 

EDIT: changed WHERE clause to look for values less than 10 digits (since the cut off values are 9)

Edited May 21, 2014 by Psycho

[Psycho]

OK, apparently cleints-old will not work in that query because of the dash. You need to enclose the field name in back ticks.

 

SELECT c.firstname, c.surname, c.phone AS current_phone, o.phone AS original_phone
 
FROM clients c
JOIN `clients-old` o
  ON c.firstname = o.firstname
 AND c.surname   = o.surname
 
WHERE LEFT(o.phone, LENGTH(c.phone)) = c.phone
  AND LENGTH(c.phone) < 10

 

Anyway, once you've found the right WHERE condition to include just the records to be updated, you would modify the query to perform the actual update like so

 

UPDATE clients c
JOIN `clients-old` o
  ON c.firstname = o.firstname
 AND c.surname   = o.surname
SET c.phone = o.phone
WHERE LEFT(o.phone, LENGTH(c.phone)) = c.phone
  AND LENGTH(c.phone) < 10

[mkc]

Okay, thank you so much for your help, I'll try it and report back.

[mkc]

Hmm the syntax is definitely correct but it's not returning any values. 

 

For simplicity of error checking I've reduced clients-old to just two records, one user with a correct 11 digit phone number, and one user with the cut off 9 digit phone number, which should be the only one returned.

 

It doesn't seem to be returning either however. I've checked the names and they are identical, no typos. Any ideas?

[Psycho]

It doesn't seem to be returning either however. I've checked the names and they are identical, no typos. Any ideas?

Without being able to inspect the data myself, no. I created two tables with the fields referenced above and actually tested this: both the SELECT and the UPDATE and both worked.

 

If you remove the WHERE portion of the query, are any records returned? If no, then the problem is with the JOIN. If you do get results, the problem is with the WHERE condition.

[mkc]

I'd made a mistake on one of the databases entries, that actually works perfectly. Thank you very much for that, that's solved a problem and helped me to learn something!