title: MySQL - Update column value based on joined table column value
body: <p>I've been reading through SO and other sites, and have followed a few examples; however, my SQL statments is still not performing as required. </p>
  
  <p>I have two tables</p>
  
  <hr>
  
  <pre><code>          parts
  ============================
  pmkParts    fnkManufacturer
  ----------------------------
     0          Penn-Union
     1          Schneider
     2         Telemecanique
  </code></pre>
  
  <p>and</p>
  
  <pre><code>         manufacturer   
  ===============================
  Manufacturer    pmkManufacturer
  -------------------------------
   Penn-Union          45
   Schneider           56
  Telemecanique        12
  </code></pre>
  
  <hr>
  
  <p>I want to change the parts table into </p>
  
  <pre><code>          parts
  ============================
  pmkParts    fnkManufacturer
  ----------------------------
     0             45
     1             56
     2             12
  </code></pre>
  
  <p>Here is the SQL statement I tried. </p>
  
  <pre><code>Update parts
  SET parts.fnkManufacturer = (
     SELECT manufacturer.pmkManufacturer
     FROM manufacturer
     WHERE manufacturer.pmkManufacturer = parts.fnkManufacturer
     )
  </code></pre>
  
  <p>That is changing the correct column, but it is filling it with 'NULLS' rather than the foreign key (manufacturer). I think there should be a join somewhere in there, but I'm not sure where.</p>
  
  <p>Any tips?</p>
  
  <h2>----------</h2>
  
  <h2>EDIT: Answer:</h2>
  
  <p>Here is the SQL statement that worked. Thanks MarcB for the help.</p>
  
  <pre><code>Update parts
  SET parts.fnkManufacturer = (
     SELECT manufacturer.pmkManufacturer
     FROM manufacturer
     WHERE manufacturer.Manufacturer= parts.fnkManufacturer
     )
  </code></pre>