Can anyone edify me on where my logic is faulty in either/both of the following mysql statements?

I wish to update the principal_staff_ID column in the staff_used table with the principal_staff_ID in the jobs table. These two tables are related via a common job_ID.

Fi开发者_如何学Pythonrst attempt:

update staff_used su 
set su.principal_staff_ID = (select j.principal_staff_ID
                               from job j where j.job_ID = su.job_ID);

Second attempt:

update staff_used su
join job j on j.job_ID = su.job_ID
set su.principal_staff_ID = j.principal_staff_ID;

If the second attempt isn't working, try this: (unsure if MySQL supports this syntax?)

update su
set su.principal_staff_ID = j.principal_staff_ID
from staff_used as su
inner join job j on j.job_ID = su.job_ID

The second form should work. You could try this form:

update staff_used
set su.principal_staff_ID = j.principal_staff_ID    
from staff_used su 
inner join ww_job j on j.job_ID = su.job_ID

With the first query, you must have a row in the Job table for every row in the Staff_Used table - or the DBMS will set the Staff_Used.Principal_Staff_ID value to NULL for every unmatched row. To make that form of UPDATE safe, you have to write:

UPDATE staff_used su 
   SET su.principal_staff_ID =  (SELECT j.principal_staff_ID
                                   FROM job j 
                                  WHERE j.job_ID = su.job_ID)
 WHERE su.principle_staff_ID IN (SELECT j.principal_staff_ID
                                   FROM job j 
                                  WHERE j.job_ID = su.job_ID);

The second sub-query ensures that only rows in Staff_Used with a matching row in Job are updated. It's also a good reason to prefer the join notation!