title: Update Table by getting maximum value from another table
body: <p>I have the following configuration:</p>
  
  <pre><code>CREATE TABLE `extRef` (
    `id` bigint(20) NOT NULL,
    `ref` varchar(100) NOT NULL,
    `extType` tinyint(4) NOT NULL,
    `extId` bigint(20) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  </code></pre>
  
  <p>I would like to migrate some data from this table, effectively, </p>
  
  <p>For reach unique (extType,extId) pair, get the maximum Id value.</p>
  
  <p>I have the following setup already:</p>
  
  <pre><code>CREATE TABLE cleanupTbl(
      retainableId BIGINT(20),
      extId BIGINT(20),
      extType BIGINT(20),
      PRIMARY KEY (retainableId, extId, extType)
  ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  
  INSERT INTO cleanupTbl(extId, extType )
  SELECT r.`extId`, r.`extType `
  FROM `extRef` r
  GROUP BY r.`extType `, r.`extId`
  HAVING COUNT(*) &gt; 1;
  </code></pre>
  
  <p>Now, when I try to insert the Maximum Id i have tried the following setups but none seem to be working due to errors. Can someone please help with a working statement? Thanks</p>
  
  <pre><code>UPDATE `cleanupTbl` cl
  SET cl.`retainableId` = temp.`largestId`
  FROM (
      SELECT MAX(r.`id`) AS largestId
      FROM `extRef` r
      JOIN `cleanupTbl` c
      ON  c.`extId` = r.`extId` AND c.`extType ` = r.`extType `
  ) temp;
  </code></pre>
  
  <p>or</p>
  
  <pre><code>UPDATE cl
  SET cl.`retainableId` = MAX(r.`id`)
  FROM `cleanupTbl` AS cl
  INNER JOIN `extRef` AS r
  ON cl.`extId` = r.`extId` AND cl.`extType` = r.`extType`;
  </code></pre>