How to delete duplicate from a table ?

Delete duplicates from table in SQL

There are different ways to delete duplicates from a table, we often get duplicates while loading data in tables which does not contain any primary key.

Lets say I have below table and we need to delete duplicates from the table.

EmpName
Ram
Gopi
Ram
Radha

Delete duplicates with help of temp table

We first try to load the distinct records in a temporary table and then delete all records from main table and the reload the main table from temporary table.

SELECT DISTINCT EmpName INTO #Data FROM Employee

DELETE FROM Employee

INSERT INTO Employee (EmpName)
SELECT EmpName FROM #Data

Delete duplicate using CTE

We use row_number() function and common table expression to delete duplicate.

;with cte as (
SELECT *,ROW_NUMBER() OVER (PARTITION BY EmpName ORDER BY EmpName) AS RNK
FROM Employee
)
DELETE FROM cte WHERE RNK > 1




1 comment:

  1. Good Post! Thank you so much for sharing this pretty post,
    Power BI Online Training from OnlineITGuru will help you to get all the required knowledge and guidance to become an expert in Power BI Course. Join india's best Power BI Online Training India institute now and get your desired job. For more info about Power BI Online Training India.
    power bi training | power bi online course

    ReplyDelete

Write a python program to find factorial of a number ?

Factorial  When we recursively multiply number incrementing from 1 to a given number then its called factorial of that number. We use the no...