UPDATE - EXAM 9JUN19

# 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;