Advertisement
NadezhdaGeorgieva

UPDATE - EXAM 9JUN19

Jun 17th, 2021
1,903
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 0.91 KB | None | 0 0
  1. # Update all clients which have the same id as the employee they are appointed to.
  2. # Set their employee_id with the employee with the lowest count of clients.
  3. #If there are 2 such employees with equal count of clients, take the one with the lowest id.
  4.  
  5. # Error Error Code: 1093. You can't specify target table 'ec' for update in FROM clause
  6.  
  7. UPDATE employees_clients ec
  8. SET ec.employee_id = (SELECT ec2.employee_id FROM employees_clients ec2
  9.         GROUP BY ec2.employee_id
  10.         ORDER BY COUNT(ec2.client_id), ec2.employee_id
  11.         LIMIT 1)
  12. WHERE ec.employee_id = ec.client_id;
  13.  
  14. # -----------------------------------------------------------------
  15. # Working solution
  16.  
  17. UPDATE employees_clients ec
  18. JOIN (SELECT ec2.employee_id FROM employees_clients ec2
  19.         GROUP BY ec2.employee_id
  20.         ORDER BY COUNT(ec2.client_id), ec2.employee_id
  21.         LIMIT 1) AS result
  22. SET ec.employee_id = result.employee_id
  23. WHERE ec.employee_id = ec.client_id;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement