How to use AVG( ) inside WHERE condition in SQL?-CodePudding

I was trying to update Employee_Age from 'Employee' table by increasing 10 years whose ages are not equal to average age.

I did try like this:

UPDATE Employee
SET Employee_Age = Employee_Age   10
WHERE Employee_Age != (SELECT AVG(Employee_Age) FROM Employee)

But I get an error:

#1093 - You can't specify target table 'Employee' for update in FROM clause

CodePudding user response：

The basic problem, is that with every update, you would get different results.

so use a temporary table like

UPDATE Employee
SET Employee_Age = Employee_Age   10
WHERE Employee_Age != (SELECT AVG(Employee_Age) FROM (SELECT * FROM Employee) t1)