Thursday, March 28, 2019

Delete Duplicate Rows in SQL Server using Common Table Expression

Introduction:

Sometimes, some duplicate data may be inserted in the SQL table. But it's harmful to you and your application too. So how do you remove the duplicate rows in your table?

Description

Take a look at the following table content. It has duplicate rows.

IdNameDepartment
1Arka.NET
2Ashok.NET
3AnubhavPHP
4Arka.NET
5SudeshnaGraphics

Row 1 and 4 are duplicates. So we have to remove one row and keep one in the table. Now the question is how to do this?

Using the Code

To solve this problem, take a look at the following code. This will solve this problem.

WITH tblTemp as
(
SELECT ROW_NUMBER() Over(PARTITION BY Name,Department ORDER BY Name)
   As RowNumber,* FROM <table_name>
)
DELETE FROM tblTemp where RowNumber >1

After running, this code shows the table data, it will ensure that all unique rows are present.

20 comments:

  1. Replies
    1. IEEE Final Year Project centers make amazing deep learning final year projects ideas for final year students Final Year Projects for CSE to training and develop their deep learning experience and talents.

      IEEE Final Year projects Project Centers in India are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation.

      corporate training in chennai corporate training in chennai

      corporate training companies in india corporate training companies in india

      corporate training companies in chennai corporate training companies in chennai

      I have read your blog its very attractive and impressive. I like it your blog. Digital Marketing Company in Chennai

      Delete
  2. This comment has been removed by the author.

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

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

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

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

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

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

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

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

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

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

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

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

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

    ReplyDelete
  16. I got what i am seraching from last few days in your Blog. I hope you will share more info about it. Please keep sharing.
    Laptop Service center in Ameerpet
    Dell Service center in Ameerpet
    HP Service center in Ameerpet
    Lenovo Service center in Ameerpet

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

    ReplyDelete
  18. UNIRAJ BCOM 3rd Year Result 2020
    UOK BCOM 3rd Year Result 2020
    ju BCOM 3rd Year Result 2020
    You’d outstanding guidelines there. I did a search about the field and identified that very likely the majority will agree with your web page.

    ReplyDelete
  19. Here is the site(http://www.bcomexamresult.in) where you get all Bcom Exam Results. This site helps to clear your all query.

    Sdsuv University BCOM 3rd Year Result 2020
    Vikram University BCOM 3rd Year Result 2020
    BA 3rd year Result 2019-20

    ReplyDelete

Follow by Email

Popular Posts

Pageviews