title: Update a foreign key by matching a column other than primary key in another table
body: <p>I am trying to update the <code>FooID</code> column in <code>Table_One</code> with a <code>FooID</code> from table two. I have the primary key for the rows I want to update for <code>Table One</code>, but I need to get that ID from <code>Table_Two</code> where a given <code>Description</code> exists. So say I know that <code>Table_One Primary_ID = 3</code> needs to have a <code>FooID</code> of 3, because <code>FooID</code> of <code>Table_Two</code> has a description of "Bar." What kind of SQL statement can I use to match the ID based on the value of the <code>Description</code>?</p>
  
  <p>Table_One</p>
  
  <pre><code>+-----------+---------+------+-----+---------+-------+
  | Field     | Type    | Null | Key | Default | Extra |
  +-----------+---------+------+-----+---------+-------+
  | Primary_ID| int(11) | NO   | PRI | NULL    |       |
  | Statement | text    | YES  |     | NULL    |       |
  | FooID     | int(11) | YES  | MUL | NULL    |       |
  +-----------+---------+------+-----+---------+-------+
  </code></pre>
  
  <p>Table_Two</p>
  
  <pre><code>+------------+--------------+------+-----+---------+----------------+
  | Field      | Type         | Null | Key | Default | Extra          |
  +------------+--------------+------+-----+---------+----------------+
  | FooID      | int(11)      | NO   | PRI | NULL    | auto_increment |
  | Description| varchar(140) | NO   |     | NULL    |                |
  +------------+--------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>So far I have tried a few things similar to:</p>
  
  <pre><code>UPDATE Table_one
  INNER JOIN Table_Two ON (Table_One.FooID = Table_Two.FooID AND Table_Two.Description =     'Bar')
  SET ksa.FooID = competency.FooID;
  </code></pre>
  
  <p>As well as:</p>
  
  <pre><code>UPDATE Table_One
  SET
  `FooID` = [SELECT FooID FROM Table_Two]
  WHERE `Table_Two`.`Description` = 'Bar';
  </code></pre>
  
  <p>The first attempt returned zero results, and the second attempt kept saying the the column in <code>Table_Two</code> does not exist. I tried a few other things as well but nothing has worked. These were the closest I came to something working.</p>