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.

21 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  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. 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
  16. This comment has been removed by the author.

    ReplyDelete
  17. 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
  18. 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

Popular Posts

Pageviews