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

Share on facebook
Share on twitter
Share on linkedin
Share on pinterest

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.
[yikes-mailchimp form=”1″]

Share on facebook
Share on twitter
Share on linkedin
Share on pinterest
Share on whatsapp
Share on email
Author Bio

Leave a Reply

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

Recommended for you
Connect With Us
Stay Connected

Subscribe to our newsletter today to receive updates on latest tech news, tutorials SEO tips many more.

Latest Articles
Technology

Global restaurant management software market

Global restaurant management software market was valued US$ 2.43 Bn in 2017 and is expected to reach US$ 7.32 Bn by 2026, at a CAGR of 14.78% during a forecast period.

Free hub for guest blogging nerds and newbie writers

  • Submit Guest Post
  • Share your Knowledge
  • Build your Backlinks
  • Advertise your products
  • Make money by writing
Subscribe to our newsletter today to receive updates on latest tech news, tutorials SEO tips many more.