title: Attempting to update table data from another table SQL #SQL
body: <p>I have two tables, one called whoop_data with fields (id, user_id, date, recovery, sleep, strain)
  and another called whoop_user with fields (id, name, average_recovery, average_sleep, average_strain)</p>
  <p>I'd like to update the whoop_user average data fields using the whoop_data that is logged.  Below are my attempts to do so.</p>
  <p>New to SQL - please be kind :)</p>
  <p>The code below results in the following table (works as intended):</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>name</th>
  <th>AVG(recovery)</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Name2</td>
  <td>39.5</td>
  </tr>
  <tr>
  <td>Name1</td>
  <td>78</td>
  </tr>
  <tr>
  <td>Name3</td>
  <td>49.5</td>
  </tr>
  </tbody>
  </table>
  </div>
  <pre><code>/*works as intended*/
  SELECT name, AVG(recovery) FROM whoop_users JOIN whoop_data
  ON whoop_users.id = whoop_data.user_id
  GROUP BY name;
  </code></pre>
  <p>Both of the attempts below result in the following table:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Name</th>
  <th>average_recovery</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Name1</td>
  <td>39.5</td>
  </tr>
  <tr>
  <td>Name2</td>
  <td>39.5</td>
  </tr>
  <tr>
  <td>Name3</td>
  <td>39.5</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I see what is happening, but I don't understand why, how to fix it, or why the statement above DO work as intended and the ones below don't.</p>
  <pre><code> /*attempt 1*/
  UPDATE whoop_users 
  SET 
      average_recovery = (SELECT AVG(recovery)
      FROM whoop_users JOIN whoop_data ON
      whoop_users.id = whoop_data.user_id
      GROUP BY name)
  WHERE 
      EXISTS (
          SELECT * FROM whoop_data
          WHERE whoop_data.user_id = whoop_users.id );
          
  SELECT * FROM whoop_users;
  /*attempt 2*/
  WITH a AS
  (
      SELECT AVG(recovery) as av
      FROM whoop_users JOIN whoop_data 
      ON whoop_users.id = whoop_data.user_id
      GROUP BY name
  )
  UPDATE whoop_users
  SET
      average_recovery = (SELECT av FROM a);
      
  SELECT * FROM whoop_users;
  </code></pre>