Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table in example is has ID as Identity Column and Columns which have duplicate data are DuplicateColumn1, DuplicateColumn2 and DuplicateColumn3.
DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)
Watch the view to see the above concept in action:
[youtube=http://www.youtube.com/watch?v=ioDJ0xVOHDY]
Reference : Pinal Dave (https://blog.sqlauthority.com)
450 Comments. Leave new
You could use CTE to delete the duplicates if you have SQL 2005 or above.
Create the duplicate table: ———————————–
IF OBJECT_ID(‘SalesHistory’) IS NOT NULLDROP TABLE SalesHistoryCREATE TABLE [dbo].[SalesHistory] ( [Product] [varchar](10) NULL, [SaleDate] [datetime] NULL, [SalePrice] [money] NULL ) GOINSERT INTO SalesHistory(Product, SaleDate, SalePrice)SELECT ‘Computer’,’1919-03-18 00:00:00.000′,1008.00UNION ALLSELECT ‘BigScreen’,’1927-03-18 00:00:00.000′,91.00UNION ALLSELECT ‘PoolTable’,’1927-04-01 00:00:00.000′,139.00UNION ALLSELECT ‘Computer’,’1919-03-18 00:00:00.000′,1008.00UNION ALLSELECT ‘BigScreen’,’1927-03-25 00:00:00.000′,92.00UNION ALLSELECT ‘PoolTable’,’1927-03-25 00:00:00.000′,108.00UNION ALLSELECT ‘Computer’,’1919-04-01 00:00:00.000′,150.00UNION ALLSELECT ‘BigScreen’,’1927-04-01 00:00:00.000′, 123.00UNION ALLSELECT ‘PoolTable’,’1927-04-01 00:00:00.000′, 139.00UNION ALLSELECT ‘Computer’,’1919-04-08 00:00:00.000′, 168.00
=======================================
Remove the duplicates —————————————
;WITH SalesCTE(Product, SaleDate, SalePrice, Ranking)AS(SELECT Product, SaleDate, SalePrice,Ranking = DENSE_RANK() OVER(PARTITION BY Product, SaleDate, SalePrice ORDER BY NEWID() ASC)FROM SalesHistory)DELETE FROM SalesCTEWHERE Ranking > 1
Pinal,
Thanks a ton, I spent a day experimenting with how to do this before I came across your solution, I added a key to my table and this worked like a charm.
I want to delete the duplicate record ( i.e i have 10 duplicate record , i want to delete a particular record leaving all the remaining 9 records means , how can i doit , plz reply me )
please clarify your question which particular record u want to delete from 10 record. then i will give u answer.
if u want delete only single record out of ten record then fallow this: ( with respect to oracle database)
1)
delete from table_name
where Rowid in (select max (Rowid) from from table_name
group by all_duplicate_column;
(e.g here table_name: Emp , all_duplicate_column: Emp_id)
note: u can use min() fun at max place result ud be same
2)
delete from table_name x
where Rowid in (select max (Rowid) from from table_name y
where x.rowid = y.rowid);
Note: both quary delete only 1 record and return 9 dublicate record
HI Manoj, you can do so by setting row count 1 and put the delete statement, this way you will delete only 1 record of the 10 duplicate records. Avineet
Very nice and I am proud of you. I am very excited.
Hi Sir
i use Microsoft access 2003
how to make the same record not to calculate the total or can i count the same record and devided by the count so that i can get original sum account
because duplicate record sum it all up
which i only want to have only one record
delete from tablename where NOT IN ( Select distinct * from tablename)
Hi Pinal
How are you , You have all the good articale
My question is that
I have table look like
ID Name
1 Deepak
1 Deepak
1 Deepak
1 Deepak
1 Deepak
how to write a single query
Remain one Row eccept All row has been Deleted
You can use the following query which has been published in the above posts:-
CREATE TABLE #Table3 (col1 int, col2 int)
INSERT INTO #Table3 VALUES (1, 1000)
INSERT INTO #Table3 VALUES (1, 1000)
INSERT INTO #Table3 VALUES (1, 1000)
INSERT INTO #Table3 VALUES (1, 1000)
INSERT INTO #Table3 VALUES (1, 1000)
INSERT INTO #Table3 VALUES (1, 1000)
SELECT * FROM #Table3;
WITH T1 AS (SELECT (ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col1)) AS RNum FROM #Table3)
DELETE FROM T1 WHERE RNum IN (SELECT a.RNum FROM T1 AS a, T1 AS b WHERE a.RNum > b.RNum GROUP BY a.RNum)
SELECT * FROM #Table3
Hi I want to get duplicate records from the table but condition is,
suppose there r 3 columns A,B,C & in that
A column has data like…kiran is working.. & in B column data is …ramesh is working…..
I want to delete particular record i.e delete only common data like ” is working” from both A & B column…
Any body help me the same….
Thanks in Advance….
kiran…
hi,
Pinal can u please tell me about to getting olny duplicate records.
iam able to get duplicate decords from my table and but there was an unique identifier so we are getting only on record the duplicate record will not be get retrived
hi
i applied query in my database as
delete from table where id not in (select max(id) from table group by duplicate column name………)
but its not working in sql server 2000 .
this query is only useful for oracle where we can replace id as row id .
so please help me if any one have ans of this question?
Deep
DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn2)
This qyery is not working can any one tell me why we are using id inthis qury and in oracle it is rowid. so please give me same query to resolve the issue.
Hi,
I have 2 tables. I would like to merge the two tables and remove any duplicates of email address.
Table 1: Purchaser (Coy id, coy name & email add)
Table 2: Member Table (Coy id, coy name & email add)
Tried using the UNION sql doesnt work and group by as well.
Hi,
WE can get the distinct from the union of these two tables as below:
SELECT DISTINCT * FROM
(SELECT * FROM Purchaser
UNION
SELECT * FROM Member)
Another new method in SQL Server 2008 is using MERGE clause.
Regards,
Pinal Dave
Hi Pinal,
Thanks for the fast respond :)
Tried the one you advise as well. Still unsuccessful
—————————————————————————
SELECT DISTINCT * FROM
(SELECT [Test_Acctron Mbr Email].company_id,[Test_Acctron Mbr Email].company_name, [Test_Acctron Mbr Email].contact_email1
FROM [Test_Acctron Mbr Email]
UNION
SELECT [Publication Purchasers List].CoyID, [Publication Purchasers List].CoyName ,[Publication Purchasers List].Email1
FROM [Publication Purchasers List])
——————————————————————
My Original : – Seems “syntax error in FROM Clause”
=============================
SELECT P.CoyID,P.CoyName,P.Email
FROM [SELECT Max([Test_Acctron Mbr Email].company_id) AS CoyID, Max([Test_Acctron Mbr Email].company_name) AS CoyName, [Test_Acctron Mbr Email].contact_email1 AS Email
FROM [Test_Acctron Mbr Email]
UNION
SELECT Max([Publication Purchasers List].CoyID) AS CoyID, Max([Publication Purchasers List].CoyName) AS CoyName, [Publication Purchasers List.Email] AS Email
FROM [Publication Purchasers List]]. AS P
GROUP BY P.email;
=============================
Hi,
We are missing the derived table name. So use the below query:
SELECT DISTINCT * FROM
(SELECT [Test_Acctron Mbr Email].company_id,[Test_Acctron Mbr Email].company_name, [Test_Acctron Mbr Email].contact_email1
FROM [Test_Acctron Mbr Email]
UNION
SELECT [Publication Purchasers List].CoyID, [Publication Purchasers List].CoyName ,[Publication Purchasers List].Email1
FROM [Publication Purchasers List]) as tmp
Regards,
Pinal Dave
Apply the same theory and it works : ) Thanks Pinal.
hi,
The problem is i am having the columns of products_model, products_id(primary key), products_last_modified_date……etc
Here i have duplicate records of products_model, so i needs to remove old duplicate records and retain the new records based on products_last_modified_date column
kindly replay me
Thanks
@Rathin
Check if this helps…
— ** WORKS ONLY IN SQL SERVER 2005 **
declare @Example1 table
( Products_id int –constraint PK_Products_id primary key
,Products_model varchar(50) NULL
, Products_last_modified_date datetime NULL)
— Nothing Loaded
select * from @Example1
insert into @Example1 values (1, ‘ABC’, ‘1/1/2000’)
insert into @Example1 values (23, ‘ABC’, ‘2/1/2000’)
insert into @Example1 values (34, ‘ABC’, ‘3/1/2000’) — This is Latest Record
insert into @Example1 values (46, ‘DEF’, ‘1/1/2000’)
insert into @Example1 values (51, ‘DEF’, ‘3/1/2000’)– This is Latest Record
insert into @Example1 values (63, ‘DEF’, ‘2/1/2000’)
— Everything Loaded
select * from @Example1
— Deleting duplicates, given condition: dont delete latest record.
— Logic: Picking Max of dates and comparing max date with all dates for that specific record, if max date is given date that record will be ignored otherwise it will be deleted.
Delete D From
(select Products_id
,Products_model
,Products_last_modified_date
,MAX(Products_last_modified_date) OVER (PARTITION BY Products_Model )MAX_DATETIME_STAMP
from @Example1) D
Where Products_last_modified_date MAX_DATETIME_STAMP
— ** IMPORTANT ** —
— Please add your complete logic to OVER PARTITION BY
— After Delete
select * from @Example1
~ IM.