How to mark counted rows as deleted? contin'd from previous question

Question

Ok I just realized that from my previous question: http://stackoverflow.com/questions/1590370/update-main-table-based-on-rows-from-a-secondary-table

That was how to update the main table with the count using the secondary table, but since this query will be in a sql job, how can I delete the rows I just counted, and making sure I don't delete any new rows inserted into the secondary table?

Answer 1

If I'm reading it right, you want to (in your first question) update the SelectionCount with the number of unique IPs associated with each EmployeeID, and now in the same process clear out the records from the Selection table that made up those count - while simultaneously not touching any records added since the SelectionCount was updated. This would be a lot easier if your selection table had 1) a primary key, and 2) a date the row was created. The absence of both complicates things.

So we'll do this a slightly odd way (assuming you're not able to change your schema). This assumes you're using SQL 2005 or 2008 :

I'm guessing your datatypes here

DECLARE @TempIPs TABLE(EmployeeID int, ipaddress varchar(25))

DELETE FROM Selection
OUTPUT deleted.EmployeeID, deleted.ipAddress
INTO @TempIPs

--replace reference to Selection table with @TempIPs which holds the legacy IPs.

UPDATE Employee
SET SelectionCount = IsNull((SELECT Count(DISTINCT ipAddress) 
    FROM @TempIPs
    WHERE @TempIPs.EmployeeID = Employee.ID), 0)

If you're planning to keep increasing the SelectionCount as new records are added, just add + SelectionCount to your UPDATE statement. You should also wrap the whole lot inside a transaction (BEGIN TRANSACTION....COMMIT TRANSACTION) statement.