Ahh, the subqueries - On Tue, Jan 15, 2019 at 5:59 PM David G. Johnston < david.g.johns...@gmail.com> wrote:
> On Tue, Jan 15, 2019 at 9:52 AM Alexander Farber > <alexander.far...@gmail.com> wrote: > >> So calculate the average somewhere else, put the result in a column, > >> and then reference that column in the SET clause. > > > > do you suggest to add a second CTE? > > That would qualify as "somewhere else" - as would a simple subquery in > FROM. > they escaped my mind for some reason! :-) WITH diffs AS ( SELECT gid, uid, played - LAG(played) OVER(PARTITION BY gid ORDER BY played) AS diff FROM moves WHERE uid = 1 ) UPDATE users SET avg_time = ( SELECT AVG(diff) FROM diffs GROUP BY uid ) WHERE uid = 1; https://www.db-fiddle.com/f/w1AYGpoZiWW9bLCYjHDk7H/1 seems to work, thank you