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) 
)
after inserting for 4 rows into the table with one duplicate row.
image showing tbl_student
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
Query result will look like this
above query result

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 table will be as shown below
method1 query result
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)
resulting table will be like this
method2 query result
after deletion


-- Done Folks--
»Keep Sharing and Learning   «

Video Tutorial

19 comments:

  1. Your thinking toward the respective issue is awesome also the idea behind the blog is very interesting which would bring a new evolution in respective field. Thanks for sharing.

    Painless Dental Treatment In Chennai

    ReplyDelete
  2. Great post!I am actually getting ready to across this information,i am very happy to this commands.Also great blog here with all of the valuable information you have.Well done,its a great knowledge.

    AWS Training in Chennai

    ReplyDelete
  3. I found your blog while searching for the updates, I am happy to be here. Very useful content and also easily understandable providing..
    Believe me I did wrote an post about tutorials for beginners with reference of your blog. 




    Selenium training in bangalore
    Selenium training in Chennai
    Selenium training in Bangalore
    Selenium training in Pune
    Selenium Online training

    ReplyDelete
  4. I am happy for sharing on this blog its awesome blog I really impressed. thanks for sharing.

    Upgrade your career Learn Data Warehousing Training in Bangalore from industry experts get Complete hands-on Training, Interview preparation, and Job Assistance at Softgen Infotech.

    ReplyDelete


  5. It is integrated on MS Windows server to facilitate creation of varied web-based applications. These applications facilitate file gathering, sharing, management and storage.




    Dot Net Training in Chennai | Dot Net Training in anna nagar | Dot Net Training in omr | Dot Net Training in porur | Dot Net Training in tambaram | Dot Net Training in velachery





    ReplyDelete
  6. I appreciate your efforts because it conveys the message of what you are trying to say. It's a great skill to make even the person who doesn't know about the subject could able to understand the subject . Your blogs are understandable and also elaborately described.

    Java training in Chennai

    Java Online training in Chennai

    Java Course in Chennai

    Best JAVA Training Institutes in Chennai

    Java training in Bangalore

    Java training in Hyderabad

    Java Training in Coimbatore

    Java Training

    Java Online Training


    ReplyDelete
  7. I found your blog while searching for the updates, I am happy to be here. Very useful content and also easily understandable providing..
    Believe me I did wrote an post about tutorials for beginners with reference of your blog.

    hadoop training in chennai

    hadoop training in omr

    salesforce training in chennai

    salesforce training in omr

    c and c plus plus course in chennai

    c and c plus plus course in omr

    machine learning training in chennai

    machine learning training in omr

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. amazing write , keep posting and if you are intresting in big data coder and code developer then checkout python classes in satara

    ReplyDelete
  10. Thanks for sharing this informative article. Your post has really made it easy to understand. I have also written some technical blogs at:

    https://www.programink.com/python-training-in-bangalore.html
    https://www.programink.com/django-training-in-bangalore.html
    https://www.programink.com/aws-training-in-bangalore.html
    https://www.programink.com/devops-training-in-bangalore.html
    https://www.programink.com/selenium-training-in-bangalore.html
    https://www.programink.com/data-science-training-courses-in-bangalore.html

    ReplyDelete