The department with the lowest average salary will receive a 10% salary increase

Question

This interview question solution doesn't work because I have two columns in the subquery and if I use IN instead of = I can't use LIMIT after the WHERE clause. I use MySQL.

UPDATE employees 
SET salary = salary + (0.10*salary) 
WHERE team = (
    SELECT team, AVG(salary) avg_sal
    FROM employee
    GROUP BY team ORDER BY avg_sal LIMIT 1)

The above query will throw the following error: SQL error [1241] [21000]: Operand should contain 1 column

If IN is used instead of = after the WHERE clause in the above query, the following error will be raised:

SQL Error [1235] [42000]: 'LIMIT & IN/ALL/ANY/SOME subquery' is not yet supported in this version of MySQL

The expected solution is as stated in the title: The department with the lowest average salary will receive a 10% salary increase

How can I rewrite this query to overcome this problem?

Answer 1

You can have the subquery return only the team instead of the two columns. Maybe, this is the query you want to write:

update employees e
set e.salary = 1.1 * e.salary
where team = (select team from employees group by team order by avg(salary) limit 1)

Unfortunately, this will throw the error :

This is a typical limitation of MySQL, which does not allow you to reopen a table that was updated in a where clause. Instead, you canjoin:

update employees e
inner join (select team from employees group by team order by avg(salary) limit 1) e1 
    on e1.team = e.team
set e.salary = 1.1 * e.salary