title: mySQL multi-table update - Update one table by picking a distinct value in another
body: <p>I have two tables:</p>
  
  <pre><code>--Table a--
  uid, name
  1, Fred Jones
  2, Mary Smith
  3, Chip Green
  etc.
  
  --Table b--
  uid, name, thing, back_ref
  1, Fred Jones, one thing, 1
  2, Fred Jones, another thing, 1
  3, Mary Smith, a thing, 2
  4, "Chip" Green, one thing, NULL
  5, "Chip" Green, another thing, NULL
  6, "Chip" Green, a third thing, NULL
  </code></pre>
  
  <p>So, there are (possibly) multiple rows in table b for each person in table a. </p>
  
  <p>I recently added the back_ref column and wanted to match names to create a back reference. I have already run an update query that used a sub-query to match the rows on the names and fill in the "back_ref" field in table b from table a's uid. Of course, since "Chip" Green's name differs slightly between a and b, that didn't happen for his rows (there was a problem with how the data was loaded before it got to me). I wrote a little function that I can use to force the connection for just the few rows that have this problem (by ignoring double-quotes and such) like this:</p>
  
  <pre><code>WHERE CleanUpName(a.name) = CleanUpName(b.name) and b.back_ref is NULL
  </code></pre>
  
  <p>I have an update statement to fill in the back_ref, but I want to write an update statement that will copy a name from any of the associated rows in table b into table a's name field to correct it (just in the cases where the comparison failed.)</p>
  
  <p>Is there a way to do this?</p>