How to remove duplicate rows?

Lincoln Burrows ~ Modified: November 17th, 2015 ~ ~ 1 Minute Reading

This topic has 2 replies, 3 voices, and was last updated 9 years ago by Stephen West.

Viewing 3 posts - 1 through 3 (of 3 total)

Author

Posts
November 17, 2015 at 6:53 am #888 Score: 0

Lincoln Burrows
Moderator

Karma: 16 pts

I need to delete the duplicate rows in a table, any idea how to do this in SQL server?

November 18, 2015 at 6:47 am #890 Score: 0
Andrew Jackson
Moderator

Karma: 1 pt
This will delete duplicate rows
```
DELETE LU 
FROM   (SELECT *, 
               Row_number() 
                 OVER ( 
                   partition BY col1, col1, col3 
                   ORDER BY rowid DESC) [Row] 
        FROM   mytable) LU 
WHERE  [row] > 1 
```
November 19, 2015 at 12:26 pm #905 Score: 0

Stephen West
Moderator

Karma: 4 pts

1. SELECT the DISTINCT records into a New Table
2. TRUNCATE the Old Table
3. MERGE the New Table back into the Old Table
Author

Posts

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.