Tuesday, September 8, 2015

How to delete duplicate row from table



SQL server tables generally doesn't contains duplicates if design done with proper addition of constraints (Primary, Unique etc...). For data integrity we should not allow duplicates to be insert into table. Sometimes duplicates inserted into table because of duplicate primary keys exist stance. If duplicate pks are present in relational database tables primary key uniqueness is not worked (or) enforced this causes duplicates insertion


Here i will show how to delete duplicates from table:-


CREATE TABLE duplicates_test(id int,name varchar(50),salary int)

insert into duplicates_test values(1,'ram',5000)
insert into duplicates_test values(2,'priya',5000)
insert into duplicates_test values(3,'amitha',4000)
insert into duplicates_test values(4,'patokar',3000)
insert into duplicates_test values(5,'jaya',3000)
insert into duplicates_test values(5,'jaya',3000)

select * from duplicates_test


now  jaya related rows existed two times we want remove it. here am using with CTE(temp table)

with x as(select *,rn=ROW_NUMBER()over(partition by id order by name) from duplicates_test)select * from x where rn>1

result

 we got duplicates rows information. now use delete instead of select see below query

with x as(select *,rn=ROW_NUMBER()over(partition by id order by name) from duplicates_test)
delete  from x where rn>1

duplicates are deleted after running above cte query