Article will cover some best practices on how to delete duplicate rows from sql server table.
We’ll be using a student table. Query for the table creation
|
1 2 3 4 5 6 |
CREATE TABLE tbl_Student ( rollNumber INT, studentName VARCHAR(MAX), address VARCHAR(MAX) ) |
Inserted 4 rows into the table, row with roll Number one is repeated twice.
following query can be used to check duplicate rows based on rollNumber,
|
1 2 3 |
SELECT DISTINCT rollnumber,COUNT(*) AS [Number of Duplcates] FROM tbl_Student GROUP BY rollNumber |
Query result will look like this
and shows that rollNumber with ‘1’ is repeated 2 times.
here I will be explaining two methods for deleting duplicate rows.
Method 1 – by keeping original
In this method all duplicate rows will be deleted except the original copy. Check this query
|
1 2 3 4 5 6 7 |
WITH CTE AS ( SELECT *,ROW_NUMBER() OVER (PARTITION BY rollNumber ORDER BY rollNumber) AS RN FROM tbl_Student ) DELETE FROM CTE WHERE RN<>1 |
after deletion, student table will be shown as below
Method 2 – without keeping original
In this method all repeated rows including original copy will be deleted.
query
|
1 2 3 4 5 6 |
WITH CTE AS (SELECT *,R=RANK() OVER (ORDER BY rollNumber) FROM tbl_Student) DELETE CTE WHERE R IN (SELECT R FROM CTE GROUP BY R HAVING COUNT(*)>1) |
resulting table will be like this
We absolutely love your blog and find the majority of your post’s to be exactly what I’m looking for. can you offer guest writers to write content in your case? I wouldn’t mind creating a post or elaborating on a number of the subjects you write in relation to here. Again, awesome website!
I am so grateful for your forum topic.Much thanks again. Will read on… Berkowitz
I really do trust each of the concepts you’ve offered on the
post. They are really convincing and can certainly work.
Nonetheless, the posts are too quick for starters. Could
you please extend them a little from subsequent time?
Thanks for your post.