title: UPDATE FROM subquery using the same table in subquery's WHERE
body: <p>I have 2 integer fields in a table "user": leg_count and leg_length. The first one stores the amount of legs of a user and the second one - their total length.</p>
  
  <p>Each leg that belongs to user is stored in separate table, as far as typical internet user can have zero to infinity legs:</p>
  
  <pre><code>CREATE TABLE legs (
      user_id int not null,
      length  int not null
  );
  </code></pre>
  
  <p>I want to recalculate the statistics for all users in one query, so I try:</p>
  
  <pre><code>UPDATE users SET
      leg_count = subquery.count, leg_length = subquery.length
  FROM (
      SELECT COUNT(*) as count, SUM(length) as length FROM legs WHERE legs.user_id = users.id
  ) AS subquery;
  </code></pre>
  
  <p>and get "subquery in FROM cannot refer to other relations of same query level" error.</p>
  
  <p>So I have to do</p>
  
  <pre><code>UPDATE users SET
      leg_count =  (SELECT COUNT(*)    FROM legs WHERE legs.user_id = users.id),
      leg_length = (SELECT SUM(length) FROM legs WHERE legs.user_id = users.id)
  </code></pre>
  
  <p>what makes database to perform 2 SELECT's for each row, although, required data could be calculated in one SELECT:</p>
  
  <pre><code>SELECT COUNT(*), SUM(length) FROM legs;
  </code></pre>
  
  <p>Is it possible to optimize my UPDATE query to use only one SELECT subquery?</p>
  
  <p>I use PostgreSQL, but I beleive, the solution exists for any SQL dialect.</p>
  
  <p>TIA.</p>