How can you delete duplicate rows from a table in SQL?

How to delete duplicate rows in SQL table? Can I delete duplicate rows without a primary key? How to Select only unique rows using CTE?

 For the demonstration purpose, I have created a dummy database with employee table. The table only has 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

 

To select the unique rows, you can change the delete statement instead of deleting the rows. You can use delete statement with select * statement. And then, change the where condition to less than 2 instead of greater than 1.

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

 

Do you think that the article was helpful in solving your problem on how to delete duplicate rows in sql? Feel free to provide your comments and concerns in the below comment section.

Close

About The Author

Deependra Kushwah
Deependra kushwah is a member of the fastest growing bloggers community "betechnical", Author, Youtuber, and hardcore Coder. I love writing code in different languages, I also write blogs on tech tutorials, gadgets review and also post some technical videos on youtube on many topics.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.