Re: aggregate functions are not allowed in UPDATE

[Alexander Farber <alexander.farber@xxxxxxxxx>]

Thank you, the following seems to have worked -

On Tue, Jan 15, 2019 at 8:49 PM David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:
>
> UPDATE users
> SET avg_time = diffs.average_time_for_the_grouped_by_user
> FROM diffs
> WHERE users.uid = diffs.uid --< the missing "where" I commented about earlier
>
> But you need to construct the "diffs" CTE/subquery so that it Group[s] By uid
>

WITH diffs AS (
  SELECT
      gid,
      uid,
      played - LAG(played) OVER(PARTITION BY gid ORDER BY played) AS diff
  FROM moves
),
avg_diffs AS (
  SELECT uid, AVG(diff) as avg_diff FROM diffs GROUP BY uid
)
UPDATE users SET avg_time = avg_diff
FROM avg_diffs
WHERE users.uid = avg_diffs.uid;

https://www.db-fiddle.com/f/w1AYGpoZiWW9bLCYjHDk7H/9

Or did you mean something else?

Regards
Alex