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 after the LIMIT 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?
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
You can have the subquery return only the team instead of the two columns. Maybe, this is the query you want to write:
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
whereclause. 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