How to delete completely duplicate rows

Question

Say i have duplicate rows in my table and well my database design is of 3rd class :-

Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (1,'Cinthol','cosmetic soap','soap');
Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (1,'Cinthol','cosmetic soap','soap');
Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (1,'Cinthol','cosmetic soap','soap');
Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (1,'Lux','cosmetic soap','soap');
Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (1,'Crowning Glory','cosmetic soap','soap');
Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (2,'Cinthol','nice soap','soap');
Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (3,'Lux','nice soap','soap');
Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (3,'Lux','nice soap','soap');

I want only 1 instance of each row should be present in my table. Thus 2nd, 3rd and last row whcih are completely identical should be deleted. What query can i write for this? Can it be done without creating temp tables? Just in one single query?

Thanks in advance :)

The first record based on insertion order? For what version of SQL Server? — OMG Ponies, Jul 27 '10 at 15:41
I'm going to guess that you mean 3rd normal form when you say 3rd class. If it's allowing complete duplicates in a table, it's not in 3NF by definition ;) — Tom H, Jul 27 '10 at 15:47

score 18 · Accepted Answer · answered Jul 27 '10 at 15:51

18

Try this - it will delete all duplicates from your table:

;WITH duplicates AS
(
    SELECT 
       ProductID, ProductName, Description, Category,
       ROW_NUMBER() OVER (PARTITION BY ProductID, ProductName
                          ORDER BY ProductID) 'RowNum'
    FROM dbo.tblProduct
)
DELETE FROM duplicates
WHERE RowNum > 1
GO

SELECT * FROM dbo.tblProduct
GO

Your duplicates should be gone now: output is:

ProductID   ProductName   DESCRIPTION        Category
   1          Cinthol         cosmetic soap      soap
   1          Lux             cosmetic soap      soap
   1          Crowning Glory  cosmetic soap      soap
   2          Cinthol         nice soap          soap
   3          Lux             nice soap          soap

answered Jul 27 '10 at 15:51

marc_s

732,580
175
1,330
1,459

Nice Marc_s, is this a CTE query? If so, is it not necessary in CTE query to have a `union` clause? – TCM Jul 27 '10 at 15:58
@Nitesh Panchal: yes, CTE's are one of the underused features of SQL Server - as is the OVER() clause :-) – marc_s Jul 27 '10 at 15:59
3

+1: I wasn't sure that you could issue a delete against a CTE like that, and before I could test it you had your answer posted :) – Tom H Jul 27 '10 at 16:08
@Tom H. Even i wasn't sure that Delete could be issued in CTE. I was under the impression that CTE's are only used for recursive queries. – TCM Jul 27 '10 at 16:14

score 4 · Answer 2 · edited May 23 '17 at 12:01

4

DELETE tblProduct 
FROM tblProduct 
LEFT OUTER JOIN (
   SELECT MIN(ProductId) as ProductId, ProductName, Description, Category
   FROM tblProduct 
   GROUP BY ProductName, Description, Category
) as KeepRows ON
   tblProduct.ProductId= KeepRows.ProductId
WHERE
   KeepRows.ProductId IS NULL

Stolen from How can I remove duplicate rows?

UPDATE:

This will only work if ProductId is a Primary Key (which it is not). You are better off using @marc_s' method, but I'll leave this up in case someone using a PK comes across this post.

edited May 23 '17 at 12:01

Community

1
1

answered Jul 27 '10 at 15:40

Abe Miessler

82,532
99
305
486

1

@Abe: `rowid` was the primary key for the table; I thought this was Oracle syntax for a moment till I saw the link. – OMG Ponies Jul 27 '10 at 15:43
I was assuming that ProductId was a primary key in his table. I've updated it with his column names to help avoid any confusion. – Abe Miessler Jul 27 '10 at 16:01
@Abe Miessler, i thought it would work but it did sound confusing to me. So i tested in Managemenet Studio and indeed it is not working. It says 0 rows deleted. Can you fix the query? – TCM Jul 27 '10 at 16:23
@Nitesh, I assumed (mistakenly) that ProductId would be a unique identifier. Since it is not you are better off using @marc_s' method. Sorry for the confusion! – Abe Miessler Jul 27 '10 at 16:29

BenAlabaster · Answer 3 · 2010-07-27T16:27:27.067

I had to do this a few weeks back... what version of SQL Server are you using? In SQL Server 2005 and up, you can use Row_Number as part of your select, and only select where Row_Number is 1. I forget the exact syntax, but it's well documented... something along the lines of:

Select t0.ProductID, 
       t0.ProductName, 
       t0.Description, 
       t0.Category
Into   tblCleanData
From   (
    Select ProductID, 
           ProductName, 
           Description, 
           Category, 
           Row_Number() Over (
               Partition By ProductID, 
                            ProductName, 
                            Description, 
                            Category
               Order By     ProductID,
                            ProductName,
                            Description,
                            Category
           ) As RowNumber
    From   MyTable
) As t0
Where t0.RowNumber = 1

Check out http://msdn.microsoft.com/en-us/library/ms186734.aspx, that should get you going in the right direction.

score 0 · Answer 4 · answered Jul 27 '10 at 15:37

0

First use a SELECT... INTO:

SELECT DISTINCT ProductID, ProductName, Description, Category
    INTO tblProductClean
    FROM tblProduct

The drop the first table.

answered Jul 27 '10 at 15:37

eykanal

26,437
19
82
113

5

From the OP: "Can it be done without creating temp tables? Just in one single query?" – dcp Jul 27 '10 at 15:38

How to delete completely duplicate rows

4 Answers4

Linked