[SOLVED] Update avg value in table

Update avg value in table

I'm trying to update my mark average by adding to it some value.

My table of admitted student contains (NOSTUDENT, COURSECODE, SEMESTER, NOGROUPE,MARK) Columns.

My View: AverageByGroupe contains the average of mark of students following that course in corresponding semester.

CREATE OR REPLACE VIEW AverageByGroup  AS
SELECT COURSCODE, NOGROUPE, SEMESTER, AVG(MARK) AS AVGMARK
FROM ADMITED_TABLE GROUP BY COURSECODE,NOGROUPE,SEMESTER;

Question: I want to update the average mark for a given course, group, semester by 10, but AVGMARK is note a column, What it the correct UPDATE syntax.

What I have tried:

UPDATE ADMITED_TABLE
SET AVG(MARK) = SELECT( (AVG(MARK) + 10)
FROM ADMITED_TABLE WHERE COURSCODE = 'AAAA' 
AND NOGROUP = 2
AND SEMESTER = 'AUTMN');

Error:
Error de command ligne: 2 Column: 8
Rapport of error -
Erreur SQL : ORA-00927: missing equal sign
00927. 00000 -  "missing equal sign"
*Cause:    
*Action:

Edit: To be more clear, this is a mutate table so what I want to use is a INSTEAD OF INSERT TRIGGER which fires every time the mark average is changed by adjusting that course student mark.

For the select syntax error, I included select in (

UPDATE ADMITED_TABLE
SET AVG(MARK) = (SELECT (AVG(MARK) + 10)
FROM ADMITED_TABLE WHERE COURSCODE = 'AAAA' 
AND NOGROUP = 2
AND SEMESTER = 'AUTMN');

Error:

ORA-00927: missing equal sign
00927. 00000 -  "missing equal sign"
*Cause:    
*Action:

Solution

UPDATE NewADMITED_TABLE

SET AVGMARK = (AVGMARK + 10) -- this is not a DERIVED column anymore

WHERE COURSCODE = 'AAAA'

AND NOGROUP = 2

AND SEMESTER = 'AUTMN