Update main table based on rows from a secondary table

Question

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

Employee (ID, name, selectionCount)

Selections (employeeID, ipAddress)

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.

Answer 1

Not a SQL Server expert, but something like this should work:

update Employee a
   set selectionCount = (select count(*)
                           from Selections
                          where employeeID = a.ID)

As an aside, I presume you know that design is somewhat denormalized, which may be undesirable in some situations.

Answer 2

Something like this should work:

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.

Answer 3

update Employee
set selectionCount = (select count(distinct ipAddress) 
                        from Selections 
                       where Selections.emmployeeID = Employee.ID)

Answer 4

Hi there.

Taking on board najmeddine solution, you could put that code into an insert and/or update trigger for the Selections table in SQL Server:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER UpdateSelectionCount ON Selections
    AFTER UPDATE, INSERT
AS
BEGIN
    SET NOCOUNT ON

    UPDATE
        Employee
    SET
        selectionCount = (SELECT
                              COUNT(DISTINCT ipAddress)
                          FROM
                              Selections
                          WHERE
                              Selections.EmployeeID = Employee.ID)

END
GO