how to delete duplicate record in tables ?

Bhupendra Singh

13y1.7k

I have a emp table.It`s have 1000 records.

Empid       Name       Department
1            AA        IT 
2            AB        SALES 
3            AA        IT
1            AA        IT 
2            AB        SALES 
4            CC        MR
1            AA        IT


I want to delete duplicate record.

Table take only one record of every person

Output of record like
Empid       Name       Department
1            AA        IT 
2            AB        SALES 
3            AA        IT
4            CC        MR

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Pankaj Pandey13y ago

DELETE FROM  table
 WHERE table.%%physloc%%
      NOT IN (SELECT MIN(b.%%physloc%%)               FROM   table b
              GROUP BY b.Empid , b.NAME,b.Department);

Javeed M Shaikh13y ago
try this on dev table first, not on production:

WITH list_duplicates (empid, name, dept,duplicate_count) AS
(
SELECT empid, name, dept,
ROW_NUMBER() OVER(PARTITION BY empid, name, dept ORDER BY empid, name, dept) AS duplicate_count
FROM tablename
)
DELETE
FROM list_duplicates
WHERE duplicate_count > 1