title: Updating a table with a groupwise maximum MYSQL?
body: <p>I had a table with duplicate user_ids with a many to 1 relationship with tasks and payment.</p>
  
  <p>I used a groupwise maximum on tasks to return the rows that had the highest task completion percentage. However I would like to update this new table with the highest payment completion stats from the same table.</p>
  
  <p>For example:</p>
  
  <pre><code>id    date                 taskid  payid  task_completion  pay_value
  4722  2007-11-08 16:20:14  2       3      7.14             0
  4722  2007-11-08 16:20:14  3       3      0.00             0
  4722  2007-11-08 16:20:14  5       3      0.00             0
  4722  2007-11-08 16:20:14  2       6      7.14             40
  4722  2007-11-08 16:20:14  3       6      0.00             40
  4722  2007-11-08 16:20:14  5       6      0.00             40
  4724  2007-11-20 15:32:40  4       7      25.71            105
  4726  2008-01-28 11:44:50  7       8      7.14             52
  4726  2008-01-28 11:44:50  8       8      34.29            52
  4726  2008-01-28 11:44:50  10      8      65.71            52
  4726  2008-01-28 11:44:50  7       9      7.14             24
  4726  2008-01-28 11:44:50  8       9      34.29            24
  </code></pre>
  
  <p>So I have used a groupwise maximum on task completion to create a new table (with unique id) as follows:</p>
  
  <pre><code>id    date                 taskid  payid  task_completion  pay_value
  4722  2007-11-08 16:20:14  2       3      7.14             0
  4724  2007-11-20 15:32:40  4       7      25.71            105
  4726  2008-01-28 11:44:50  10      8      65.71            52
  </code></pre>
  
  <p>The problem is that for id 4722 the highest pay_value and payid have not been inserted into the resulting table.</p>
  
  <p>I would like to update this in the form (using this select):</p>
  
  <pre><code>SELECT 
      s1.payid,
      s1.pay_value,
  FROM
      f_tbl s1
          LEFT JOIN
      f_tbl s2 ON s1.cashid = s2.cashid
          AND s1.pay_value &gt; s2.pay_value
  WHERE
      s2.cash_id IS NULL
  GROUP BY user_id;
  </code></pre>
  
  <p>However it seems that updating the final table based on this query is not possible.
  I am stumped, thank you for your assistance.</p>