CODEWITHSUNDEEP
mysqlsqljoin
Glen Lucero
Glen Lucero

Reputation: 93

MYSQL UPDATE by Join not Joining Properly

I am trying to update a column in the table that is defined in another. Essentially I am joining both tables and updating the data in column from tb1 by setting it equal to tbl2.

UPDATE phone
JOIN companyInfo
ON companyInfo.companyName = phone.companyName
SET phone.companyID = companyInfo.companyID

For some reason the companyID in phone is only being updated for some on the rows. I have roughly 58mil rows in phone tbl, each with a proper companyName that matches a companyName in companyInfo. Im not sure why only 34mil rows are being affected by the update. The same query has worked perfectly for my other tables. The structure of phone table is right, but mysql does not seem to be recognizing certain companyName in phone, meaning I cant search phone by certain company names even though those names are in the table (I can see them in the SELECT * statement). Those names are the ones that are not being recognized in the join. Can anyone shed some light on this?

Upvotes: 0

Views: 260

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270613

This is your query:

UPDATE phone JOIN
       companyInfo
       ON companyInfo.companyName = phone.companyName
    SET phone.companyID = companyInfo.companyID;

If this is not updating some rows, it is because companyName does not match. You can investigate the non-matches. Here is one way:

select p.*
from phone p left join
     companyInfo ci
     on p.companyName = ci.companyName
where cci.companyName is null;

That much is easy. Why aren't they matching? There could be a myriad of reasons, such as:

  • Spaces at the beginning of a string.
  • Different character cases in a case sensitive database/server/table
  • Letters that differ by accents
  • Abbreviations spelled out (eg. 'Co.' versus Company)

In general, company names make a very poor key for tables. I do note that you appear to have a better id and you want to use it. You will probably have to do lots of investigation, unless your company names are already verified and standardized.

Upvotes: 2

Related Questions