i cannot update a column by using a cloumn in the same table in mysql

Question

I want to calculate letter grade by using average column.

I tried to do it with this code:

UPDATE students
SET letter_grade='BA' 
WHERE (average- (select AVG(average)from   students )) > 20 
AND (average-(select AVG(average)from students)) < 25;

But it gives the following error: you cant specify target table 'students' for update if FROM clause.

It seems mysql does not allow update a table by using select from same table. How can I solve this problem?

Answer 1

Try this

UPDATE students
SET letter_grade='BA'
GROUP BY average HAVING (average - avg(average)) > 20
AND (average - avg(average)) < 25

Answer 2

You can select into a temporary table: Create a temporary table in a SELECT statement without a separate CREATE TABLE and then update with a select from the temporary.

CREATE TEMPORARY TABLE avgs( select students.id, average-AVG(average) as a from students);
UPDATE students SET letter_grade='BA' 
    WHERE EXISTS (SELECT a FROM avgs 
                  WHERE students.id =avgs.id AND -20 < a AND a < 25);

Answer 3

Or do it as two queries, something like

declare average_average float
set average_average = AVG(average) from students 
Update students Set Letter_grade = 'BA' 
where (average - average_average) > 20 and (average - average_average) < 25

assuming average is a floating point field