Why can't I make this update?-CodePudding

I am trying (SQL Server 2019) to modify the number of department of 'SAAVEDRA'. The new department will be the department with the most employees whose job is 'EMPLOYEE'

I have just made the next query but I have the error:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

UPDATE EMPLE
SET DEPT_NO = (SELECT DEPT_NO
                FROM EMPLE
                WHERE JOB='EMPLOYEE'
                GROUP BY DEPT_NO
                HAVING COUNT(SURNAME)=
                                      (SELECT MAX(COUNT(SURNAME))
                                      FROM EMPLE
                                      WHERE JOB='EMPLOYEE'
                                      GROUP BY DEPT_NO))                        
WHERE SURNAME='SAAVEDRA';

Can someone help me with this error?

thanks in advance

CodePudding user response：

Try

UPDATE EMPLE
SET DEPT_NO = (SELECT top(1) e2.DEPT_NO
                FROM EMPLE e2
                WHERE e2.JOB='EMPLOYEE'
                GROUP BY e2.DEPT_NO
                order by COUNT(e2.SURNAME) desc)                        
WHERE SURNAME='SAAVEDRA'

CodePudding user response：

Why not a slight alteration...

UPDATE EMPLE

SET DEPT_NO = (SELECT TOP 1 DEPT_NO
                FROM EMPLE
                WHERE JOB='EMPLOYEE'
                GROUP BY DEPT_NO
                ORDER BY COUNT(SURNAME) DESC )
WHERE SURNAME='SAAVEDRA';

By doing the inner query to ordering by the count DESCENDING, and applying a top 1, should work.