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.

1 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';
  • I get - [Err] 1093 - You can't specify target table 'a' for update in FROM clause
    – LOSTinDB
    Jul 25, 2015 at 6:36
  • The query itself does return the right data but I can't output it to the final table.
    – LOSTinDB
    Jul 25, 2015 at 6:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.