This portal is to share my acquired knowledge related to Sql Server. I hope will be helpful for novice and professionals.

Sunday, July 18, 2010

Delete Duplicate Record using CTE?

Hi All,
I know there are many ways to delete duplicate records to table, but some times we find that there is no column to identity each record like Identity or any PK.
So handle this requirement , I think CTE is best option here..
Try sql:

----Delete Duplicate
Create Table Product
(
Product Varchar(100),
Price Int
)

Insert Into Product
Select 'Sugar','100'
UNION ALL
Select 'Salt','50'
UNION ALL
Select 'Refined','300'
UNION ALL
Select 'Sugar','100'
UNION ALL
Select 'Salt','50'
UNION ALL
Select 'Juice','75'

UNION ALL
Select 'Juice','75'

Select *,'Before' from Product;

WITH CTE (ID,Product,Price)
AS
(
Select ROW_Number () OVER(Partition By Product Order By Product) AS ID,*
from Product
)
Delete
From CTE
Where ID >1;

Here Row_Numer function generates unique number for each partition and then we can simply delete the records having ID value > 1 and solve our problem.

Thanks...

1 comment:

jampa said...

nice example..Thank you!!