Wednesday, April 20, 2016
Saturday, April 25, 2015
Delete Duplicate Rows in Sql Server
Please find Original post from here
In this article, i would like to share a tip for deleting duplicate rows from sql server table.
For that we will be using a student table created from following query
CREATE TABLE tbl_Student ( rollNumber INT, studentName VARCHAR(MAX), address VARCHAR(MAX) )
tbl_Student |
following query will be helpful to know is there any duplicate rows based on rollNumber,
SELECT DISTINCT rollnumber,COUNT(*) AS [Number of Duplcates] FROM tbl_Student GROUP BY rollNumber
and shows that rollNumber with '1' is repeated twice
Now i am going to delete this duplicate rows and this can be done in two method using CTE
Please find Original post from here
Method 1 - by keeping original
In this method all duplicate rows will be deleted by preserving original copy and my query looks like this
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 |
Please find Original post from here
Method 2 - without keeping original
Here we will delete all repeated rows including original copy and here is the query
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)
after deletion |
-- Done Folks--
»Keep Sharing and Learning ☺ «
Video Tutorial
Video Tutorial
Subscribe to:
Posts (Atom)