title: mySQL - update multiple columns with a select returning multiple rows
body: <p>I have a table of postcodes and I want to update each postcode with its 3 nearest neighbours. Ie to fill in the blanks in this table:</p>
  
  <pre><code>postcode  nearestPostcode1  nearestPostcode2  nearestPostcode3
  _______________________________________________________________
  
  KY6 1DA      -                -                  -
  KY6 1DG      -                -                  -
  KY6 2DT      -                -                  -
  KY6 1RG      -                -                  -
  ....
  </code></pre>
  
  <p>I've figured out a SELECT query to find the nearest postcodes and here is one clumsy way the first row could be updated:</p>
  
  <pre><code>update table1 set 
  nearestPostcode1 = (select query for returning the first nearest postcode),
  nearestPostcode2 = (select query for returning the second nearest postcode),
  nearestPostcode3 = (select query for returning the third nearest postcode)
  where postcode = 'KY6 1DA';
  </code></pre>
  
  <p>However this will result in 3 select queries being run for each row update. It would be more efficient if there was some way to do what is expressed by this pseudo code:</p>
  
  <pre><code>update table1 set 
  (nearestPostcode1, nearestPostcode2, nearestPostcode3) = 
  (select query to return the 3 nearest postcodes)
  where postcode = 'KY6 1DA';
  </code></pre>
  
  <p>The 'select query' in the above looks like this:</p>
  
  <pre><code>select postcode from postcodeTable 
  order by &lt;equation to calculate distance&gt; ASC 
  limit 3
  </code></pre>
  
  <p>Is there anyway for the rows returned from the select to be put into a form that they can be used to update multiple fields?
  Thanks.</p>