There will be the odd occasion when you will need to delete duplicates from a table. There are a number of ways that this can be achieved, however I have found that using a CTE and ROW_NUMBER is the shortest to achieve this.
Image you have a table that has no unique identifier / primary key and you have duplicates. You don’t care which rows stay, as they are identical, but you do need to remove all but one. The example below will show how the change be achieved:
CREATE TABLE #CartoonCharacters
|Dup Counter||Full Name||Programme|
|1||Eric Cartman||South Park|
|1||Homer J Simpson||Simpsons|
|2||Homer J Simpson||Simpsons|
|1||Peppa Pig||Peppa Pig|
|1||Peter Griffin||Family Guy|
|2||Peter Griffin||Family Guy|
In this example we have two duplicates. By creating a CTE that selects from the #CartoonCharacters table and also selects a row number that represents duplicates, we can delete the duplicate from the CTE which in turn deletes from the underlying table.
Note If your system only supported soft deletes you could you the same CTE to run an update rather than hard delete. e.g.