Re: aggregate functions are not allowed in UPDATE

On Tue, Jan 15, 2019 at 12:42 PM Alexander Farber
<alexander(dot)farber(at)gmail(dot)com> wrote:
>
> Last question please - how to run the query for all users?
>
> I know I could use the FOR-loop from PL/PgSQL, but is there also a pure SQL way?
>
> How to refer to the outside "uid" from inside the CTE in the query below?
>
> WITH diffs AS (
> SELECT
> gid,
> uid,
> played - LAG(played) OVER(PARTITION BY gid ORDER BY played) AS diff
> FROM moves
> WHERE uid = how_to_refer_to_the_outside_uid
> )
> UPDATE users SET avg_time =
> (
> SELECT
> AVG(diff)
> FROM diffs
> GROUP BY uid
> )
> WHERE uid IN (SELECT uid FROM users);

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

David J.