Increase salary of employees 1000 base on deparment avg highest salary

Question

My employee table structure like below image

that below query get department id base on department average highest salary

SELECT dep_id
FROM employee
GROUP BY dep_id
ORDER BY AVG( salary ) DESC
LIMIT 1 

Output

dep_id
2

but i having issue in update query

UPDATE employee
SET salary = salary +1000
where dep_id = (
SELECT dep_id
   FROM employee
   GROUP BY dep_id
   ORDER BY AVG( salary ) DESC
   LIMIT 1
)

Answer 1

The problem with your current SELECT statement is it won't be able to get multiple dep_id having the same highest average salary. I'd rather do it this way to get the dep_id,

SELECT  dep_id
FROM    employee
GROUP   BY dep_id
HAVING  AVG(salary) = 
        (
            SELECT  AVG(salary) avg_sal
            FROM    employee
            GROUP   BY dep_id
            ORDER   BY avg_sal DESC
            LIMIT   1
        )

• SQLFiddle Demo

then you can now update the salary for each emp_id having the highest average salary.

UPDATE  employee a
        INNER JOIN
        (
            SELECT  dep_id
            FROM    employee
            GROUP   BY dep_id
            HAVING  AVG(salary) = 
                    (
                        SELECT  AVG(salary) avg_sal
                        FROM    employee
                        GROUP   BY dep_id
                        ORDER   BY avg_sal DESC
                        LIMIT   1
                    )
        ) b ON a.dep_id = b.dep_id
SET     a.salary = a.salary + 1000

• SQLFiddle Demo

Answer 2

UPDATE employee e1
JOIN (SELECT dep_id, AVG(salary) avsal
      FROM employee
      GROUP BY dep_id
      ORDER BY avsal DESC
      LIMIT 1) e2
USING (dep_id)
SET e1.salary = e1.salary + 1000

SQLFIDDLE