Re: aggregate functions are not allowed in UPDATE - Mailing list pgsql-general

On Tue, Jan 15, 2019 at 12:42 PM Alexander Farber
<alexander.farber@gmail.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.