Updating column values of Oracle 11g HR schema

Question

I'm trying to update the salaries of all employees to the maximum salary of the department in which s/he works.

ERD:

My try:

UPDATE EMPLOYEES SET
(SELECT SALARY
FROM EMPLOYEES
GROUP BY SALARY, DEPARTMENT_ID) = 
(SELECT "MAX"(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID);

Obviously this is wrong. It gives the error:

ORA-01747: invalid user.table.column, table.column, or column specification

How to do this correctly?

Answer 1

you can use merge

   MERGE INTO EMPLOYEES a
    USING ( SELECT DEPARTMENT_ID, MAX(SALARY) as sal
                      FROM EMPLOYEES group by DEPARTMENT_ID
         ) b
    ON ( a.DEPARTMENT_ID = b.DEPARTMENT_ID )
    WHEN MATCHED THEN 
    UPDATE SET  a.SALARY = b.sal

Answer 2

You can use a correlated subquery:

UPDATE EMPLOYEES
    SET SALARY = (SELECT MAX(SALARY)
                  FROM EMPLOYEES E2
                  WHERE E2.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID
                 );