Introduction
Here I will explain how to Delete Duplicate Record or Rows from Table in SQL Server. I am not going in detail or background of the article its a common problem which occurs time to time with developers so here i just explain how solve your problem.
SQL query to delete duplicate rows
create a table like this,
create table Emp(empid int,name varchar(20))
Table Emp
|
empid
|
name
|
|
1
|
abc
|
|
1
|
def
|
|
2
|
abc
|
|
2
|
abc
|
Enter some random or duplicate value in table:
Method 1:
- select distinct * into #tmptbl From Emp
- delete from Emp
- insert into Emp
- select * from #tmptbl drop table #tmptbl
If you want to consider only few columns in a table for duplication criteria to delete rows then Method 1 will not work
(in example, if EMP table has more than 2 columns and delete rows if empid and name repeats more than one time).
Method 2:
You can do with CTE (Common Table Expression).
- WITH cte AS (
- SELECT empid , name ,
- row_number() OVER(PARTITION BY empid , name order by empid ) AS [rn]
- FROM dbo.Emp
- )
- DELETE cte WHERE [rn] > 1
If you want to delete all the rows if the selected columns repeated more than 1 time then use below query.
Method 3:
- delete from Emp where empid in(select empid from Emp group by empid having count(*) >1)