MySQL - compare 2 tables and update the 2nd from the 1st-CodePudding

I have 2 tables that I'm importing from CSV files, companies (8000 records) and contacts (11000 records). The contacts have company names which match the names in the company table. After I've inserted the records I need to add the companyID to the contacts table.

a_test_company
 --------------- --------------- 
| companyID     | companyName   | 
 --------------- --------------- 
| 1             | companyA      | 
| 2             | companyB      | 
| 3             | companyC      | 
| 4             | companyD      |  
 --------------- --------------- 

a_test_contact
 --------------- --------------- --------------- --------------- 
| contactID     | contactName   | companyName   | companyID     |
 --------------- --------------- --------------- --------------- 
| 1             | contactA      | companyA      |  NULL         |
| 2             | contactB      | companyA      |  NULL         |
| 3             | contactC      | companyB      |  NULL         |
| 4             | contactD      | companyC      |  NULL         |
| 5             | contactE      | companyC      |  NULL         |
 --------------- --------------- --------------- --------------- 


Expected Result:
a_test_contact
 --------------- --------------- --------------- --------------- 
| contactID     | contactName   | companyName   | companyID     |
 --------------- --------------- --------------- --------------- 
| 1             | contactA      | companyA      | 1             |
| 2             | contactB      | companyA      | 1             |
| 3             | contactC      | companyB      | 2             |
| 4             | contactD      | companyC      | 3             |
| 5             | contactE      | companyC      | 3             |
 --------------- --------------- --------------- ---------------

I can do this in PHP using a SELECT and UPDATE loop, but I'd like to know how to do it in PHPmyAdmin too. I've tried using the code example from this post: SQL Select rows from 1st table and Update into 2nd Table by substituting my names but I get an error from MySQL:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM a_test_company
JOIN a_test_company
ON a_test_contact.companyName = a_test' at line 3

Sample Code from post: (He used countries and cities)

UPDATE ci
SET ci.CountryId = co.CountryId
FROM tblCity ci
JOIN tblCountry co
ON ci.ISO2 = co.CountryISO2
AND ci.ISO3 = co.CountryISO3
WHERE ci.CountryId IS NULL

My 1st attempt: (I substituted countries for companies and cities for contacts)

UPDATE ci
SET ci.companyID = co.companyID
FROM a_test_contact ci
JOIN a_test_company co
ON ci.companyName = co.companyName
WHERE ci.companyID IS NULL

My 2nd attempt, same error: (I didn't understand the ci and co, although I'm assuming it was names for city and country)

UPDATE a_test_contact
SET a_test_contact.companyID = a_test_company.companyID
FROM a_test_contact
JOIN a_test_company
ON a_test_contact.companyName = a_test_company.companyName
WHERE a_test_contact.companyID IS NULL

CodePudding user response：

A simple update with join clause is needed:

update a_test_contact cont 
inner join a_test_company com on cont.companyName=com.companyName
set cont.companyID=com.companyID;

https://dbfiddle.uk/K8ouXdF6

Learn more on MySQL UPDATE JOIN

I didn't understand the ci and co, although I'm assuming it was names for city and country

They are known as Alias which improve the readability of the queries. In my example I used cont for a_test_contact table and com for a_test_company table