match the names

Question

I have two tables. CustomersTable and BusinessDirectory
Both tables have a column called businessName.
my CustomerTable has a columns cid, customerID, businessName my BusinessDirectory table has columns bdid, businessName, getID

I want to update the getID field in BusinessDirectory table by customerID in CustomerTable where the business name matches in CustomerTable. Hence I did this query

update BusinessDirectory INNER JOIN CustomerTable ON CustomerTable.businessName = BusinessDirectory.businessName set BusinessDirectory.getID = CustomerTable.customerID;

which updates the records fine as long as the records match 100%. There are some records where there is a little typo and stuff like I have a business name General Contractors Inc in one table and the other table has it as General Contractors. As you can see its missing Inc, so it doesnt match. What can I do to get best possible matches. Thanks

Answer 1

This should work, but it is not particularly safe:

UPDATE BusinessDirectory, CustomerTable 
SET BusinessDirectory.getID = CustomerTable.customerID
WHERE BusinessDirectory.businessName Like
Left(CustomerTable.businessName,InstrRev(CustomerTable.businessName," ")) & "*"