CODEWITHSUNDEEP
mysqlsql-updateaveragewhere-clause
Hasibur Rahman
Hasibur Rahman

Reputation: 28

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

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

Upvotes: 1

Views: 70

Answers (1)

nbk
nbk

Reputation: 49373

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)

Upvotes: 1

Related Questions