Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- # Update all clients which have the same id as the employee they are appointed to.
- # Set their employee_id with the employee with the lowest count of clients.
- #If there are 2 such employees with equal count of clients, take the one with the lowest id.
- # Error Error Code: 1093. You can't specify target table 'ec' for update in FROM clause
- UPDATE employees_clients ec
- SET ec.employee_id = (SELECT ec2.employee_id FROM employees_clients ec2
- GROUP BY ec2.employee_id
- ORDER BY COUNT(ec2.client_id), ec2.employee_id
- LIMIT 1)
- WHERE ec.employee_id = ec.client_id;
- # -----------------------------------------------------------------
- # Working solution
- UPDATE employees_clients ec
- JOIN (SELECT ec2.employee_id FROM employees_clients ec2
- GROUP BY ec2.employee_id
- ORDER BY COUNT(ec2.client_id), ec2.employee_id
- LIMIT 1) AS result
- SET ec.employee_id = result.employee_id
- WHERE ec.employee_id = ec.client_id;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement