Saturday, December 9, 2017

How to remove duplicate record in sql table

Hello Friends here are example of how to remove duplicate record in table in SQL server

 This is a table with duplicate record

SELECT * FROM PRODUCTMASTER
PD_ID PD_NAME PD_AMOUNT
1 PRD1 500
2 PRD2 550
3 PRD3 500
5 PRD3 500



Fist of all we are find row number using ROW_NUMBER() Funcation

SELECT
ROW_NUMBER() OVER(ORDER BY PD_NAME) AS SRNO,
* FROM PRODUCTMASTER


SRNO PD_ID PD_NAME PD_AMOUNT
1 1 PRD1 500
2 2 PRD2 550
3 3 PRD3 500
4 5 PRD3 500


Now we can PARTITION duplicate record using PARTITION

SELECT
ROW_NUMBER() OVER(PARTITION BY PD_NAME  ORDER BY PD_NAME) AS SRNO,
* FROM PRODUCTMASTER


SRNOPD_IDPD_NAMEPD_AMOUNT
11PRD1500
12PRD2550
13PRD3500
25PRD3500

Then after run this queury for delete duplicate record

WITH TBL AS (
SELECT
ROW_NUMBER() OVER(PARTITION BY PD_NAME  ORDER BY PD_NAME) AS SRNO,
* FROM PRODUCTMASTER
)
DELETE FROM TBL WHERE SRNO !=1

4 comments: