Postgres Pro
Downloads
Home   >   mailing lists

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

From Alexander Farber
Subject Re: aggregate functions are not allowed in UPDATE
Date
Msg-id CAADeyWhb9FDLUX=c0quhONxzrkp9j9Gf2yr=Xp0f2Qautiu+oQ@mail.gmail.com
Whole thread Raw
In response to Re: aggregate functions are not allowed in UPDATE  ("David G. Johnston" <david.g.johnston@gmail.com>)
Responses Re: aggregate functions are not allowed in UPDATE  (Alexander Farber <alexander.farber@gmail.com>)
List pgsql-general
Tree view
Ahh, the subqueries -

On Tue, Jan 15, 2019 at 5:59 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Jan 15, 2019 at 9:52 AM Alexander Farber
<alexander.farber@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;


seems to work, thank you

pgsql-general by date:

Previous
From: Rob Sargent
Date:
Subject: Re: Refining query statement
Next
From: Alexander Farber
Date:
Subject: Re: aggregate functions are not allowed in UPDATE