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?

There is no question in this post. Please either ask re 1 bad query/function with obligatory [mre], including why you think it should return something else or are unsure at the 1st subexpression that it doesn't give what you expect or are stuck, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do with justification & a [mre]. Then misunderstood code doesn't belong. But please ask about unexpected behaviour 1st because misconceptions get in the way of your goal. [ask] [Help] Basic questions are faqs. — philipxy, May 02 '23 at 20:20
[Fetch the row which has the Max value for a column](https://stackoverflow.com/q/121387/3404097) — philipxy, May 11 '23 at 00:47

score 0 · Accepted Answer · answered May 02 '23 at 14:21

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

I was referring to the order in which they are displayed above.. :-) Interestingly I did not get an error with either of your queries. MySQL Ver 8.0.32-0ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu)) — Codistan, May 02 '23 at 15:14

Increase salary by 10% for department with lowest average salary

1 Answers1