Re: aggregate functions are not allowed in UPDATE

Thank you, the following seems to have worked -

On Tue, Jan 15, 2019 at 8:49 PM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> 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