Technical Articles

Delete Duplicate rows in SQL ?

0 0
// Creating table and inserting some records
create table email_list
(
name varchar(100),
email varchar(50)
)
go
insert into email_list values (‘himanshu’, ‘him@gmail.com’);
insert into email_list values (‘prem’, ‘prem@gmail.com’);
insert into email_list values (‘himanshu’, ‘him@gmail.com’);
insert into email_list values (‘prem’, ‘prem@gmail.com’);
insert into email_list values (‘himanshu’, ‘him@gmail.com’);



// Showing records
select * from email_list
order by 1,2



// Deleting Duplicate rows
with list_duplicate( name, email, duplicate_count)as
(select name, email, ROW_NUMBER() over(PARTITION By name , email order by name, email) as duplicate_count from email_list)
delete from list_duplicate where duplicate_count>1

Subscribe
Notify of
guest

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x
× How can I help you?