PostgreSQL update table with average vale from another table

Question

I have tried this three different ways and PostgreSQL still doesn't like that I am "aggregate functions are not allowed in UPDATE". I am not sure how to this without doing that. Can someone point me in the right direction to populate my average Wage field with my average wages?

Attempt 1:

UPDATE public."JobCategory"
    SET "AverageWage" = round(avg("Wage"), 4)
FROM public."Employees" WERE "Wage" > 0

Attempt 2:

UPDATE public."JobCategory" c
INNER JOIN (
  SELECT round(avg("Wage"), 4) as average
  FROM public."Employees"
) x ON c."Index" = x."JobIndex"
SET c."AverageWage" = x.average

Attempt 3:

UPDATE public."JobCategory" AS v 
SET "AverageWage" = s.round(avg("Wage"), 4)
FROM public."Employees" AS s
WHERE v."Index" = s."JobIndex"

This answer might be closely related, https://stackoverflow.com/a/22205793/5781248 — J.J. Hakala, Apr 20 '18 at 21:22

score 1 · Accepted Answer · answered Apr 20 '18 at 23:30

1

You can do this with a subquery:

WITH subq AS (
    SELECT round(avg(Wage), 4) as average
    FROM public.Employees
)
UPDATE public.JobCategory jc
SET AverageWage = subq.average
FROM subq

answered Apr 20 '18 at 23:30

BShaps

1,344
7
17

PostgreSQL update table with average vale from another table

1 Answers1