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
Fist of all we are find row number using ROW_NUMBER() Funcation
SELECT
ROW_NUMBER() OVER(ORDER BY PD_NAME) AS SRNO,
* FROM PRODUCTMASTER
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
| SRNO | PD_ID | PD_NAME | PD_AMOUNT |
| 1 | 1 | PRD1 | 500 |
| 1 | 2 | PRD2 | 550 |
| 1 | 3 | PRD3 | 500 |
| 2 | 5 | PRD3 | 500 |
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
Nice one great example.....
ReplyDeleteGrate Example it's work properly
ReplyDeleteVery good with example and it works properly.
ReplyDeleteGood
ReplyDelete