Is there any way to store a value from a query then use in the next query below it?

Question

i have a Postgres SQL database that has quizes, questions, answers and marks on there (multiple tables) im trying to write a query to work out the maxscore for one quiz and then update the quiz table using the query thats just calculated the max score

Heres my querys

SELECT SUM( maxscore)
FROM (
  SELECT max(answer.answermark) AS maxscore
  FROM answer, questions
  WHERE questions.quizid = 1 AND answer.questionid = questions.questionid
  GROUP BY answer.questionid
) scr;

UPDATE quiz
SET maxscore = '50'
WHERE quizid = 1

where "SET maxscore = '50'" i need to instead of typing 50 i need to use the calculation from the query above, is there any way ??

Thanks

Answer 1

try with cte:

with v as (
SELECT SUM( maxscore)
FROM (
  SELECT max(answer.answermark) AS maxscore
  FROM answer, questions
  WHERE questions.quizid = 1 AND answer.questionid = questions.questionid
  GROUP BY answer.questionid
)
UPDATE quiz
SET maxscore = v.sum
from v
WHERE quizid = 1

Answer 2

In this case you do not need to keep state. Do it in from clause:

update quiz
set maxscore = scr.maxscore
from (
    select sum(maxscore) as maxscore
    from (
        select max(a.answermark) as maxscore
        from
            answer a
            inner join
            questions q using (questionid)
        where q.quizid = 1
        group by questionid
    ) s
) scr
where quizid = 1

If you want to do it for all quizid:

update quiz
set maxscore = scr.maxscore
from (
    select quizid, sum(maxscore) as maxscore
    from (
        select quizid, max(a.answermark) as maxscore
        from
            answer a
            inner join
            questions q using (questionid)
        group by quizid, questionid
    ) s
    group by quizid
) scr
where quiz.quizid = scr.quizid