Well this one is an interview question:
The below 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 employees GROUP BY team ORDER BY avg_sal LIMIT 1)
Please feel free to help me learn better. TIA
>Solution :
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