UPDATE FROM subquery using the same table in subquery's WHERE

Question

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.

Each leg that belongs to user is stored in separate table, as far as typical internet user can have zero to infinity legs:

CREATE TABLE legs (
    user_id int not null,
    length  int not null
);

I want to recalculate the statistics for all users in one query, so I try:

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;

and get "subquery in FROM cannot refer to other relations of same query level" error.

So I have to do

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)

what makes database to perform 2 SELECT's for each row, although, required data could be calculated in one SELECT:

SELECT COUNT(*), SUM(length) FROM legs;

Is it possible to optimize my UPDATE query to use only one SELECT subquery?

I use PostgreSQL, but I beleive, the solution exists for any SQL dialect.

TIA.

Answer 1

I would do:

WITH stats AS
   ( SELECT COUNT(*)    AS cnt
          , SUM(length) AS totlength
          , user_id
       FROM legs
      GROUP BY user_id
   )
UPDATE users
   SET leg_count = cnt, leg_length = totlength
  FROM stats
 WHERE stats.user_id = users.id