Why do I get this 'GROUP BY' error when using 'UPDATE' (SQL)?

Question

I'm trying to increase the employee salaries by 50% for all employees except those currently earning the highest salary:

UPDATE employees
SET emp_salary= emp_salary + (emp_salary * 50)/100
GROUP BY emp_salary
HAVING emp_salary < (select max(emp_salary) from employees);

But I get this error: ERROR: syntax error at or near "GROUP" LINE 3: GROUP BY emp_salary

Thank you!

Answer 1

As far as I know you cannot use update with group by. Instead, you can do your grouping in a subquery:

UPDATE E1
SET 
    emp_salary= emp_salary + (emp_salary * 50)/100
FROM
    employees E1
    INNER JOIN 
    (
        -- Not really grouping anything here - so just a pointless example!
        SELECT emp_id
        FROM employees
        GROUP BY emp_salary
        HAVING emp_salary < (select max(emp_salary) from employees)
    ) E2
    ON E1.emp_id = E2.emp_id;

As accurately noted by Stu you are not really doing something here that requires grouping, so more simply and effectively in this case just:

UPDATE employees
SET 
    emp_salary= emp_salary + (emp_salary * 50)/100
WHERE
    emp_salary < (select max(emp_salary) from employees);