title: MySQL UPDATE - MAX() GROUP BY
body: <p>How do I change:</p>
  
  <pre><code>SELECT user.postcode, max(postcode.postcode) as postcode
  FROM user
  INNER JOIN postcode ON user.postcode LIKE CONCAT( postcode.postcode,  "%" )
  GROUP BY user.postcode
  </code></pre>
  
  <p>into an UPDATE similar to?</p>
  
  <pre><code>UPDATE user 
  INNER JOIN postcode ON user.postcode LIKE CONCAT(postcode.postcode, "%") 
  SET user.lat = postcode.lat, user.lng = postcode.lng
  </code></pre>
  
  <p>I cannot work out how to factor in the MAX() and GROUP BY</p>
  
  <h1>Answer</h1>
  
  <p>Below is my version of @ceteras code. It works great!</p>
  
  <pre><code>DROP TABLE IF EXISTS tmp;
  CREATE TABLE tmp AS
  SELECT user.user_id, max(postcode.postcode) AS postcode, postcode.lat, postcode.lng
  FROM user
  INNER JOIN postcode ON user.postcode LIKE CONCAT( postcode.postcode,  "%" )
  GROUP BY user.postcode;
  ALTER TABLE tmp ADD PRIMARY KEY(user_id);
  
  UPDATE user 
  INNER JOIN tmp ON user.user_id = tmp.user_id
  SET user.lat1 = tmp.lat, user.lng1 = tmp.lng;
  
  DROP TABLE tmp;
  </code></pre>