Web Analytics Made Easy - Statcounter

Technology with Vivek Johari

Interview Questions SQL Advanced SQL CTE SQL Server

Deleting Duplicate rows using CTE

Byvivekjohari

Dec 29, 2013 #SQL CTE

Deleting Duplicate rows using CTE

Many times we caught in a situation where we have a table where there is no primary or unique column defined in it and rows with duplicate data inserted in the table. In this example I will tries to show how to delete the duplicate rows using CTE.

Suppose we have a table name “tbl_dublicatedata” whose table creation script is given below:-

Create table tbl_dublicatedata (name nvarchar(100), age int, salary int)

Now we insert duplicate data into this table. Insert script for these rows are given below:-

Insert  into tbl_dublicatedata
select ‘Vivek’, 28, 800000
union all
select ‘Avinash’, 29, 600000
union all
select ‘Vivek’, 28, 800000

If we want to see the data into the table , we can run the following query

select * from  tbl_dublicatedata

This query will return the following result

With the help of the CTE we can remove these duplicate records.

Trending
Importance of Best Practices in database programming

With cte_duplicate (name, age, salary, rownumber)
as (
select name,age,salary, row_number()over(partition by name, age , salary order by name, age , salary)as rank from tbl_dublicatedata
)
delete from cte_duplicate where rownumber<>1

If we again see the data in the table tbl_dublicatedata using the same query which we used earlier we will get the following result:-

select * from  tbl_dublicatedata

From the result we can see that the duplicate rows are deleted and there is no duplicate data in the table.

DMCA.com

Discover more from Technology with Vivek Johari

Subscribe to get the latest posts sent to your email.

By vivekjohari

I am currently working as a Senior Database Professional and have around 18 years of experience in database. Degree:- Master Degree in Computer(MCA) Certification course in Data Science & Machine Learning from Indian Institute of Technology (IIT), Delhi Work experience:- Designing of the database. Database Optimization. Writing Complex Stored Procedures,Functions,Triggers etc. Designing and developing SSIS & DTS packages. Designing SQL Reports using SSRS. Database Server Maintenance. Certification:- MCTS: DA-100: Analysing Data with Microsoft Power BI MCTS: DP-300: Administering Relational Databases on Microsoft Azure Microsoft certified Sql DBA in Sql server 2008 (MCTS). Microsoft certified BI professional in Sql server 2008 (MCTS). Oracle certified profession DBA in ORACLE 10g (OCP) certified profession DBA in ORACLE 9i (OCP) My other publication Technical Blog:- Technologies with Vivek Johari Guest Author and Blogger at sqlservercentral.com

Related Post

Miscellaneous SQL Server

🎉✨ Happy New Year 2025! ✨🎉

Jan 1, 2025 vivekjohari
SQL Server

Database Partitioning in SQL Server

Dec 31, 2024 vivekjohari
SQL Server

What are the important python libraries?

Dec 31, 2024 vivekjohari
26 thought on “Deleting Duplicate rows using CTE”

  1. The “where” clause in the CTE delete is wrong, the field is called “rank” inside the cte but in the where is used “rownumber”

    Reply

  18. … [Trackback]

    […] Here you will find 74810 additional Info on that Topic: techmixing.com/2013/12/deleting-duplicate-rows-using-cte.html […]

    Reply

Leave a Reply

You missed

Miscellaneous SQL Server

🎉✨ Happy New Year 2025! ✨🎉

January 1, 2025 vivekjohari No Comments
SQL Server

Database Partitioning in SQL Server

December 31, 2024 vivekjohari No Comments
SQL Server

What are the important python libraries?

December 31, 2024 vivekjohari No Comments
SQL Server

Understanding Estimated and Actual Query Plans in SQL Server

December 31, 2024 vivekjohari No Comments

Discover more from Technology with Vivek Johari

Subscribe now to keep reading and get access to the full archive.

Continue reading