更新基于行的主表从辅助表 [英] Update main table based on rows from a secondary table

问题描述

我有一个员工平局类型的应用程序，该表如下：

I have a employee draw type application, the tables look like:

Employee (ID, name, selectionCount)

Selections (employeeID, ipAddress)

现在我需要一个更新查询，将选择计算每个雇员的数量（以独特的IPaddresses），并更新Employee表的selectionCount列。

Now I need an update query that will count the number of selections for each employeeID (with unique IPaddresses), and update the selectionCount column in the table Employee.

推荐答案

这样的事情应该工作：

WITH SelectionCounts(EmployeeId, SelectionCount)
AS
(
    SELECT s.EmployeeId, COUNT(DISTINCT IpAddress) AS SelectionCount
    FROM Selections s
    GROUP BY s.EmployeeId
)
UPDATE Employee
SET SelectionCount = sc.SelectionCount
FROM SelectionCounts sc
WHERE ID = sc.EmployeeId

没有测试这样的语法可能不完全正确的。

Did not test it so the syntax may not be completely correct.

这篇关于更新基于行的主表从辅助表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！