CODEWITHSUNDEEP
mysqldatabase
J. Hasan
J. Hasan

Reputation: 512

MySQL query giving 5% bonus to employee whose salary is less than average salary

I'm a beginner in MySQL. I have a table named Employee and have a column Salary. I'm trying to give 5% bonus to employee table whose salary is below average salary. I don't know where i did mistakes. Can anyone tell me where's the wrong?

I tried this SQL: update employee set salary = salary * 1.05 where salary < (select avg(salary) from employee)

Upvotes: 0

Views: 4974

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

You can also get around this error using a join:

UPDATE employee e1
INNER JOIN (SELECT AVG(salary) AS avg_salary FROM employee) e2
SET salary = 1.05 * salary
WHERE salary > avg_salary;

Upvotes: 2

forpas
forpas

Reputation: 164089

Mysql has this weird feature for UPDATE statements and you can't directly refer to the updated table, so you need to nest the subquery inside another:

update employee 
set salary = salary * 1.05 
where salary < (select salary from (select avg(salary) salary from employee) t )

Upvotes: 1

Related Questions