Skip to content
Advertisements

Delete duplicate rows from SQL table

  • Delete duplicate rows from SQL table.
  • How to delete duplicate rows without a primary key?
  • Select only unique rows using CTE.

 For the demonstration purpose, I have created a dummy database with employee table. which has only name column with multiple duplicate values.

Scripts for dummy data

create database demo;

create table Emp(name varchar(12))

insert into  Emp values('a')
insert into  Emp values('b')
insert into  Emp values('b')

Capture

As you can see in the above Emp table only one column is available, with multiple duplicate values. our task is to remove the duplicate values and keep only unique values in the table.

Not to worry, with the help of CTE or common table expression, we can easily achieve this.

use Demo;

with EmpCte as 
(
select *,ROW_NUMBER() over(partition by name order by name) as row_num from Emp
)
delete from EmpCte where row_num>1

 

In case you want to select the unique rows instead of deleting the rows. you need to change the delete statement with the select * statement and change the where condition from greater then 1 to less then 2.

use Demo;

with EmpCte as 
(
select *,ROW_NUMBER() over(partition by name order by name) as row_num from Emp
)
select * from EmpCte where row_num<2

 

Advertisements

Deependra Kushwah View All

Deependra is a Senior Developer with Microsoft technologies, currently working with Opteamix India business private solution. In My Free time, I write blogs and make technical youtube videos. Having the good understanding of Service-oriented architect, Designing microservices using domain driven design.

What you think

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: