SteveOH

– Initiate transaction – if we fail, it won’t commit the changes
– Declare the variables to store the values returned by FETCH.
DECLARE @accountId varchar(40), @appearances int

– Get the recordset indicating the AccountId with duplicate entries
DECLARE duplicate_cursor CURSOR FOR
SELECT AccountID, COUNT(AccountID) AS namecount
FROM warehouse
GROUP BY AccountID
HAVING COUNT(AccountID) > 1

– Open the recordset
OPEN duplicate_cursor

– Perform the first fetch and store the values in variables.
FETCH NEXT FROM duplicate_cursor
INTO @accountId, @appearances

– Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
– delete all records for this accountId minus 1
– Determine how many records must be deleted
SET @appearances = @appearances – 1

– Limit the result of this delete to the above calculated maximum
SET ROWCOUNT @appearances

– Execute the delete
DELETE warehouse
WHERE AccountID = @accountId

FETCH NEXT FROM duplicate_cursor
INTO @accountId, @appearances
END

CLOSE duplicate_cursor
DEALLOCATE duplicate_cursor

– Reset the rowcount limits
SET ROWCOUNT 0
GO