Update MySQL table from another table

Databases
1

I inherited a database from an old project and would like to make the data more robust. Currently it stores a group of Jobs and each job has a customer listed, however the customer name is stored as a string value and there is a separate “Customer” table that I would prefer to instead of a join of the customer number rather than their name as this causes the data to not be as clean as the user could enter in whatever they like so we could have jobs listed under “ABC Widgets” and “ABC Widgets, Co” and they would appear to be 2 different customers.

I’ve created a new field in the Jobs table to store the customer ID from the customer table, what I am now trying to figure out is how best to build the query to populate this new field for all the existing jobs (I’ll be rewriting the other parts of the code to utilize customer numbers for additions/updates). So from my PHP Admin what would the query be to place the customer ID from the Customers table into the Jobs table?

UPDATE ‘Jobs’ SET CustNum = Customer.ID WHERE Customer.Name = Jobs.CustomerName is where I am starting, but I think I am missing something. Most likely a JOIN (which I am still having some trouble fully grasping). Or is a JOIN even required since I can just call from the different tables? I want this to iterate through all the jobs and if it can’t find a match, then just leave the CustNum field blank so I can manually resolve those.

Greg

2

yes, a join is required

UPDATE jobs
INNER
  JOIN customer
    ON customer.name = jobs.customername 
   SET jobs.custnum = customer.id
3

Thanks! That did exactly what I needed.
Greg