Increase salary by 10% for department with lowest average salary

Question

This interview question solution does not work because I have two columns in the subquery and I can't use LIMIT if I use IN in place of = following the WHERE clause. I'm on 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 raise the following error: SQL Error [1241] [21000]: Operand should contain 1 column(s)

If IN is used following the WHERE clause instead of = in the query above then it would raise the below error:

SQL Error [1235] [42000]: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Expected solution as stated in the title: Increase salary by 10% for department with lowest average salary

How can I rewrite this query to overcome this?

Answer 1

You can have the subquery return just the team instead of two columns. Probably, this is the query you wanted 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 would raise error:

You can't specify target table 'e' for update in FROM clause

That's a typical limitation of MySQL, that won't let you re-open the table that is being updated in the where clause. Instead, you can join:

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