Update - aggregation function in the where clause

Question

Why this sql instruction doesn't work...?

I want to update the salaries by 15% for all employees who have salaries lower than average ...

UPDATE Employers SET salary=salary * 1.15 WHERE salary < (SELECT AVG(salary) FROM Employers);

Thanks.

What is the error it gives you? You're missing a `)` towards the end. `(SELECT AVG(salary) FROM Employers);` — Tyler Roper, Oct 17 '16 at 15:12
Sorry :). But this is not the problem...In mysql, i have the following message: #1093 - Table 'Employers' is specified twice, both as a target for 'UPDATE' and as a separate source for data. — nosperov, Oct 17 '16 at 15:22
Unfortunately it seems like you're battling a restriction of MySQL: ["You cannot update a table and select from the same table in a subquery."](http://dev.mysql.com/doc/refman/5.7/en/update.html). Not to say there isn't a solution, personally I'm just not aware of it. Someone who knows more may be able to provide a solution for you! — Tyler Roper, Oct 17 '16 at 15:26
Thank You all! :) I find a solution. This is also like this: UPDATE Employers SET salary = salary *1.15 WHERE salary<(SELECT * FROM ( SELECT AVG(salary ) FROM Employers) AS salEmp); — nosperov, Oct 17 '16 at 19:39

score 0 · Answer 1 · answered Oct 17 '16 at 15:33

0

Could be you need an alias

  UPDATE Employers
  SET salary=salary * 1.15
  CROSS JOIN ( (SELECT AVG(salary)  avg_salary FROM Employers) ) t 
  WHERE salary < t.avg_salary;

answered Oct 17 '16 at 15:33

ScaisEdge

1 Answers1