How to Delete Duplicate rows from a table ?


--//Create ProductMaster table
CREATE TABLE ProductMaster
(
    ProductID INT,
    ProductName VARCHAR(25)
)

--//Insert Rows with Duplicate Values
INSERT INTO ProductMaster
(ProductID, ProductName)
VALUES
(1, 'Product-1'),
(1, 'Product-1'),
(2, 'Product-2'),
(3, 'Product-3'),
(3, 'Product-3'),
(4, 'Product-4'),
(5, 'Product-5'),
(5, 'Product-5'),
(5, 'Product-5')

--//Let's check the values inserted
SELECT * FROM ProductMaster

--//Query to Delete Duplicate Rows
;WITH CTE_Products
AS
(
    SELECT
    ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY ProductID) AS Num,              ProductID
    FROM ProductMaster
)
DELETE FROM CTE_Products WHERE Num > 1

--//Let's check Row in the ProductMaster table
SELECT * FROM ProductMaster

--//Clean the ProductMaster Table
DROP TABLE ProductMaster



No comments:

Post a comment