How do I output the "most" in MySql?

Question

I have a list of employees for our application. One of the fields is job title. I use the 4000 job titles to roll people into 1 of 50 "job roles". We then give people access to our apps via job roles.

On this list we have their supervisor email associated too. Their supervisor can assign them something on our apps but needs a job role. Note that supervisors have a "generic manager" job title which rolls into a generic manager "job role".

So based on

employeeid supervisorid jobrole

How can I parse through my list get all of the employees under a supervisor and then assign the supervisor a "suprole" base on what jobrole most of their employees have?

Example is:

Ted, Dan, Fred, Susan, and Bill report to Robert.

Their job roles are: Ted - dishwasher Dan - cook Fred - server Susan - cook Bill - cook

I would expect Robert's "suprole" to be cook.

Answer 1

update employee as a 
 set a.suprole=(
 select b.jobroll from employee as b 
 where b.supervisorid=a.employeeid 
 group by b.jobroll 
 order by count(b.jobroll) desc 
 limit 1
 ) 
 where a.jobroll='generic manager';

• LOSTinDB: I get - [Err] 1093 - You can't specify target table 'a' for update in FROM clause
• LOSTinDB: The query itself does return the right data but I can't output it to the final table.