About Delete Command in SQL Server

Question

how to delete the duplicate rows from the table parmanently ?

I think you should clarify your question a bit with examples of what you have tried so far, and what output you are expecting. See [how to ask a question.](http://stackoverflow.com/help/how-to-ask) — Roman Marusyk, Feb 03 '16 at 10:37

score 2 · Answer 1 · edited Feb 03 '16 at 10:49

I like CTE's and ROW_NUMBER since it allows to change it easily to see which rows are deleted (or updated), therefore just change the DELETE FROM CTE... to SELECT * FROM CTE:

;WITH CTE AS (
    SELECT [col1],
           [col2],
           [col3],
           [col4],
           [col5],
           [col6],
           [col7],
           RN = ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col1)
    FROM dbo.table
)
DELETE FROM CTE
WHERE RN > 1

Try this

About Delete Command in SQL Server

1 Answers1