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 CAADeyWgOszPLJAsafV6FjH3dwKxXv8Ow07PcVQQ+xUrb5+p=-A@mail.gmail.com
Whole thread Raw
In response to Re: aggregate functions are not allowed in UPDATE  (Alexander Farber <alexander.farber@gmail.com>)
Responses Re: aggregate functions are not allowed in UPDATE  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-general
Tree view
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);


Regards
Alex

pgsql-general by date:

Previous
From: Alexander Farber
Date:
Subject: Re: aggregate functions are not allowed in UPDATE
Next
From: "Ramamoorthi, Meenakshi"
Date:
Subject: Can anyone please provide me list of customers using postgreSQL