从另一个表中的行批量更新表格

Question

两个表格：

Employees
- EmployeeID
- LeadCount


Leads
- leadID
- employeeID

我想通过计算Leads表中与EmployeeID相同的潜在客户数量，来更新Employees.LeadCount列。

注意：可能存在多个具有相同employeeID的潜在客户，因此必须进行DISTINCT(SUM(employeeID))操作。

- public static

Answer 1

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

- insin

Answer 2

您正在为数据同步问题做准备。随着Leads表中的行被插入、更新或删除，您需要持续更新Employees.LeadCount列。

最好的解决方案是完全不存储LeadCount列，而是在需要该值时使用SQL聚合查询重新计算潜在客户数量。这样它将始终是正确的。

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

另一种解决方案是在Leads表上创建INSERT、UPDATE和DELETE触发器，以便始终保持Employees.LeadCount列的最新状态。例如，使用MySQL触发器语法：

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

如果你正在使用MySQL，另一个选项是使用多表更新语法。这是MySQL对SQL的扩展，不适用于其他品牌的关系型数据库管理系统。首先将所有行中的LeadCount重置为零，然后与Leads表进行连接，并在由连接产生的每一行中递增LeadCount。

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

- Bill Karwin

---

如果不需要精确值，那么按要求运行为计划任务可能非常好。 - BCS

+1 表示不存储LeadCount，但 -1 表示触发器。我认为它们应该是分开的回复。 - David Aldridge

Answer 3

对于更新（和删除）操作，连接的工作方式与选择操作相同（至少在某些流行的关系型数据库管理系统中）：

UPDATE Employees SET
  LeadCount = Leads.LeadCount
FROM Employee
JOIN (
  SELECT EmployeeId, COUNT(*) as LeadCount 
  FROM Leads 
  GROUP BY EmployeeId
) as Leads ON
  Employee.EmployeeId = Leads.EmployeeId  

SUM(DISTINCT EmployeeId) 没有意义 - 你只需要一个 COUNT(*)。

• MS SQL Server 支持 UPDATE...FROM 和 DELETE...FROM 句法，以及 MySql，但 SQL-92 标准不支持。SQL-92 要求您使用行表达式。我知道 DB2 支持这种语法，但其他数据库我不确定。坦白地说，我觉得 SQL-92 的版本很令人困惑 - 但标准和理论狂热者会认为 FROM 语法违反关系理论，并且在 JOIN 子句不精确或切换 RDBMS 供应商时可能导致不可预测的结果。

- Mark Brackett

---

由于某种原因，更新查询比较复杂，但希望现在能以相同的方式来思考它们！ - public static

UPDATE和DELETE中的连接不是标准SQL，但一些实现支持它们作为扩展。哪个品牌的RDBMS支持上述语法？ - Bill Karwin

Answer 4

UPDATE Employees SET LeadCount = (
  SELECT Distinct(SUM(employeeID)) FROM Leads WHERE Leads.employeeId = Employees.employeeId
)

- Jason Cohen

---

sum(employeeid) 没有任何意义，而对单个值使用 distinct 总是会返回相同的值，因此它是多余的。 - Torsten Marek

如果我没记错的话，那会非常慢（MySQL 是我唯一使用过的数据库）。 - BCS

Answer 5

从上面窃取并删除依赖子查询。

// create tmp -> TBL (EmpID, count)

insert into TBL 
   SELECT employeeID COUNT(employeeID) Di
   FROM Leads WHERE Leads.employeeId = Employees.employeeId GROUP BY EmployeeId
UPDATE Employees SET LeadCount = (
  SELECT count FROM TBL WHERE TBL.EmpID = Employees.employeeId
)

// drop TBL

编辑，应该是“group by”，而不是“distinct”：b（感谢Mark Brackett）

- BCS