How to delete duplicate record from SQL Server table?

In this article I am going to explain about how to delete duplicate record from sql server table. I am using temp table concept to remove duplicate same data from sql server table.

Description

In SQL server sometimes there are duplicate records are inserted with same details. That time we need to remove all duplicate rows from table but we don't have any primary key field in that table. In that situation apply below temp table concept to find out duplicate rows and delete it from table.

Table structure


create table emp(eno int,empname varchar(50),sal bigint)

Insert table data with duplication


insert into emp values('101','James','45000')
insert into emp values('101','James','45000')
insert into emp values('102','mike','12000')
insert into emp values('102','mike','45000')
insert into emp values('102','mike','45000')
insert into emp values('103','Andrew','12000')

Find duplicate Rows

1) Find using simple query


SELECT empname, COUNT(*) FROM  emp GROUP BY empname HAVING COUNT(*) > 1

2) Find using temp table


WITH tempTable as
(
SELECT ROW_NUMBER() Over(PARTITION BY eno,empname ORDER BY eno) As RowNumber,* FROM emp
)

select * from tempTable where RowNumber >1

Delete duplicate Rows


WITH tempTable as
(
SELECT ROW_NUMBER() Over(PARTITION BY eno,empname ORDER BY eno) As RowNumber,* FROM emp
)

DELETE FROM tempTable where RowNumber >1

Conclusion

I hope above process is help you to find about duplicate rows and delete duplicate rows from sql server table.

Comments

Author: Ajesh Madhukar Dalvi13 Aug 2012 Member Level: Silver Points : 4

rno sname
—————-
1 Frieda
1 Frieda
1 Frieda
4 Nathan
6 senthil
6 senthil
6 senthil
2 Senthil
6 senthil
1 Shanoj
2 Shanoj
4 Varun

Step 1: Create duplicate table and move the duplicate value records of the original table to a duplicate table.

SELECT DISTINCT * INTO DuplicateTb FROM dublicatetest GROUP BY sname,rno HAVING COUNT(rno) > 1

After executed the above Query if you look the table the below result come.
select * from DuplicateTb
1 Frieda
6 senthil

select * from dublicatetest

2 Shanoj
1 Frieda
2 Senthil
4 Varun
6 senthil
1 Shanoj
6 senthil
4 Nathan
6 senthil
6 senthil
1 Frieda
1 Frieda

Step 2: Delete all rows from the original table that also reside in the duplicate table.

DELETE dublicatetest WHERE sname IN (SELECT sname FROM DuplicateTb)

After executed the above Query if you look the table the below result come.

select * from DuplicateTb
1 Frieda
6 senthil

select * from dublicatetest
2 Shanoj
4 Varun
1 Shanoj
4 Nathan

Step 3 : Move back the rows from duplicate table to original table.

INSERT dublicatetest SELECT * FROM DuplicateTb

After executed the above Query if you look the table the below result come.

select * from DuplicateTb
1 Frieda
6 senthil

select * from dublicatetest
2 Shanoj
1 Frieda
6 senthil
4 Varun
1 Shanoj
4 Nathan

Step4: Drop the duplicate table.

DROP TABLE DuplicateTb

Author: nuwan rathnayake23 Aug 2012 Member Level: Silver Points : 0

It work. Nice one........

Author: Md. Mizanur Rahman29 May 2013 Member Level: Silver Points : 0

this is very helpful

Author: Ranajoy28 Jul 2013 Member Level: Silver Points : 5

Hi,

Please try below sql query hope it will solve the problem

SQL query to delete duplicate rows
-----------------------------------

Following are the different methods for deleting duplicate rows

Method A:
Insert the distinct rows from the duplicate rows table to new temporary table.
Delete data from table which has duplicate rows then insert the distinct rows from the temporary table as shown below.
select distinct * into #tmp From emp
delete from emp
insert into emp
select * from #tmp drop table #tmp

Method B:
If you want to delete all the rows if the selected columns repeated more than 1 time then use following query,
Query to delete these 3 duplicated rows or repeated more than 1 time,
delete from emp where
eno in(select eno from emp group by eno having count(*) >1)

Author: Phagu Mahato19 Oct 2013 Member Level: Gold Points : 8

Most of the days, we tend to use primary key or distinctive key for preventing insertion of duplicate rows in SQL Server. however if we do not use these keys, then it's obvious that duplicate rows can be entered by the user. once inserting duplicate rows into table, it becomes a serious issue to delete those duplicate rows. in this time, we want to delete those duplicate rows to resolve the problem. therefore this subject can facilitate US to delete those duplicate rows from the particular table.

ALTER TABLE Student ADD StudentID int identity(1,1) PRIMARY KEY;

WHILE EXISTS (SELECT COUNT(*) FROM Student GROUP BY stdID, stdSSN HAVING COUNT(*) greater 1)
BEGIN
DELETE FROM Student WHERE StudentID IN
(
SELECT MIN(StudentID) as [DeleteID]
FROM Student
GROUP BY stdID, stdSSN
HAVING COUNT(*) greater 1
)
END

Delete record for SQL DataBase
DELETE FROM Student a
WHERE ROW_NUMBER() ( SELECT MIN( ROW_NUMBER() )
FROM Student b
WHERE a.StdID = b.StdID
AND a.StdSSN = b.StdSSN

There square measure 3 ways that we will get use of them so as to delete dublicates in an exceedingly table. One methodology is victimisation the SET ROWCOUNT t-sql command. and also the second methodology uses the highest tsql command. however if you open the SQL Server 2005 Books on-line (BOL) you'll see a note indicating that SET ROWCOUNT won't have an effect on DELETE, INSERT, and UPDATE statements within the next unharness of SQL Server (probably in Katmai). therefore if you're writing your sql codes additionally for next versions of SQL Server, then you ought to like selecting the second methodology and use prime in your codes. and also the third methodology is adding associate identity column to the table to tell apart identical rows within the table. this is often a little difficult methodology :)

Author: sudhanshu pal27 Jan 2014 Member Level: Silver Points : 1

DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)

Author: sudhanshu pal30 Jan 2014 Member Level: Silver Points : 4

Hello All,
Here's my solution:

Introduction
Most of the times, we use primary key or unique key for preventing insertion of duplicate rows in SQL Server. But if we don't use these keys, then it's obvious that duplicate rows could be entered by the user. After inserting duplicate rows into table, it becomes a major issue to delete those duplicate rows. In that time, we need to delete those duplicate rows to resolve the issue. So this topic will help us to delete those duplicate rows from the specific table.

Problem
Firstly, we will create a table, where we will insert some duplicate rows to understand the topic properly. Create a table called ATTENDANCE by using the following code:
CREATE TABLE [dbo].[ATTENDANCE](
[EMPLOYEE_ID] [varchar](50) NOT NULL,
[ATTENDANCE_DATE] [date] NOT NULL
) ON [PRIMARY]

Now insert some data into this table.

INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
('A001',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
('A001',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
('A002',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
('A002',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
('A002',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
('A003',CONVERT(DATETIME,'01-01-11',5))

After inserting the data, check the data of the below table. If we grouped the employee_id and attendance_date, then A001 and A002 become duplicates.

Solution
First, insert an identity column in that table by using the following code:

ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1)

Check the AUTOID column. Now we will start playing the game with this column.

Now use the following code to find out the duplicate rows that exist in the table.

SELECT * FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)

Ultimately, these are the duplicate rows which we want to delete to resolve the issue. Use the following code to resolve it.

DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)

Author: maruthanayakam21 Feb 2014 Member Level: Silver Points : 3

try this:

create table dupdata(id int,name varchar(30),sal money)
insert into dupdata values(1,'veeru',2100)
insert into dupdata values(1,'veeru',2100)
insert into dupdata values(2,'sud',2100)
insert into dupdata values(2,'sud',2100)

alter table dupdata add rowid int identity(1,1)

delete from dupdata where rowid in(select max(rowid) from dupdata group by id,name,sal

having count(*)>1)

alter table dupdata drop column rowid

select * from dupdata

Author: Aarif Shaikh14 Oct 2014 Member Level: Bronze Points : 0

Very good Article .. Thanks

Author: DHARMENDRA KUMAR20 Oct 2014 Member Level: Silver Points : 0

hi,
my solution is

SELECT *INTO TEMP FROM (SELECT DISTINCT * FROM TEST)T
DROP TABLE TEST
SELECT *INTO TEST FROM TEMP
DROP TABLE TEMP

Dharmendra

Author: macxima22 May 2015 Member Level: Gold Points : 0

If we want to drop the duplicates values from the actual table?

Please suggest the best way to handle this situation.

Author: Jayakumar.R10 Jul 2015 Member Level: Gold Points : 4

Hi
Macxima

Drop and delete Different

Drop means whole Table Destroy . If you have record have or not then you can need new table you can create new table

for ex query this

DROP TABLE Tb1

Delete means you can delete particular row in the table

Delete query this

Delete FROM tblReg WHERE Id=1

if u need duplicate delete use this

WITH tpTb as
(
SELECT ROW_NUMBER() Over(PARTITION BY id,name ORDER BY name) As RowNumber,* FROM tblReg
)

DELETE FROM tpTb where RowNumber >1

Author: Clap Creative31 Mar 2016 Member Level: Bronze Points : 0

Look at this link http://www.codeproject.com/Articles/157977/Remove-Duplicate-Rows-from-a-Table-in-SQL-Server
this is ultimate guide even in steps.

Submit Article

Return to Article Index

How to delete duplicate record from SQL Server table?

Description

Table structure

Insert table data with duplication

Find duplicate Rows

Delete duplicate Rows

Conclusion

Comments

About dotnetspider.com

Quick Links

General

Services