Bulk updating a table from rows from another table

Question

2 tables:

Employees
- EmployeeID
- LeadCount


Leads
- leadID
- employeeID

I want to update the Employees.LeadCount column by counting the # of leads in the Leads table that have the same EmployeeID.

Note: There may be more than 1 lead with the same employeeID, so I have to do a DISTINCT(SUM(employeeID)).

Answer 1

UPDATE
    Employees E
SET
    E.LeadCount = (
        SELECT COUNT(L.EmployeeID)
        FROM Leads L
        WHERE L.EmployeeID = E.EmployeeID
    )

Answer 2

You're setting yourself up for a data synchronization problem. As rows in the Leads table are inserted, updated, or deleted, you need to update the Employees.LeadCount column constantly.

The best solution would be not to store the LeadCount column at all, but recalculate the count of leads with a SQL aggregate query as you need the value. That way it'll always be correct.

SELECT employeeID, COUNT(leadId) AS LeadCount
FROM Leads
GROUP BY employeeID;

The other solution is to create triggers on the Leads table for INSERT, UPDATE, and DELETE, so that you keep the Employees.LeadCount column current all the time. For example, using MySQL trigger syntax:

CREATE TRIGGER leadIns AFTER INSERT ON Leads
FOR EACH ROW BEGIN
  UPDATE Employees SET LeadCount = LeadCount + 1 WHERE employeeID = NEW.employeeID;
END

CREATE TRIGGER leadIns AFTER UPDATE ON Leads
FOR EACH ROW BEGIN
  UPDATE Employees SET LeadCount = LeadCount - 1 WHERE employeeID = OLD.employeeID;
  UPDATE Employees SET LeadCount = LeadCount + 1 WHERE employeeID = NEW.employeeID;
END

CREATE TRIGGER leadIns AFTER DELETE ON Leads
FOR EACH ROW BEGIN
  UPDATE Employees SET LeadCount = LeadCount - 1 WHERE employeeID = OLD.employeeID;
END

Another option, if you are using MySQL, is to use multi-table UPDATE syntax. This is a MySQL extension to SQL, it's not portable to other brands of RDBMS. First, reset the LeadCount in all rows to zero, then do a join to the Leads table and increment the LeadCount in each row produced by the join.

UPDATE Employees SET LeadCount = 0;
UPDATE Employees AS e JOIN Leads AS l USING (employeeID)
  SET e.LeadCount = e.LeadCount+1;