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

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!

CodePudding user response：

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);

CodePudding user response：

You can refer GROUP BY in UPDATE FROM clause

And in this case seems, we don't need GROUP BY, you can use only WHERE clause to check the highest salary.

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