I have a table Horses which is linked by a foreign key to table RacingMaster
Horses contains only 2 columns HorseID and HorseName. Referential integrity of the key FK relationship is set to Cascade
I need to delete duplicate entries from Horses and have been using this query which I found on the net.
WITH TempEmp (Name,duplicateRecCount)
AS
(
SELECT HorseName,ROW_NUMBER() OVER(PARTITION by horsename ORDER BY horsename)
AS duplicateRecCount
FROM dbo.Horses
)
--Now Delete Duplicate Records
DELETE FROM TempEmp
WHERE duplicateRecCount > 1
Unfortunately the cascade effect on RacingMaster table is that records are lost there as well which is not what I want. If the duplicates in Horses could be done based on the newest additions to table Horses then I could ensure that no data went into RacingMaster until all duplicates had been deleted and in that way no records would be lost from RacingMaster. Is that the way to do it and if so could someone show me how please? Many thanks.