How to delete duplicate Records in SQL

Answer 1

DELETE DUPLICATE ROWS FROM A TABLE IN SQL SERVER 2005

CREATE TABLE EMPLOYEE
(
id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
EMPNAME VARCHAR(100),
SALARY DECIMAL
)

SELECT * FROM EMPLOYEE

INSERT INTO EMPLOYEE VALUES ( ‘Pawan’ , 100000 )
INSERT INTO EMPLOYEE VALUES ( ‘Pawan’ , 80000 )
INSERT INTO EMPLOYEE VALUES ( ‘Pawan’ , 40000 )
INSERT INTO EMPLOYEE VALUES ( ‘Rahul’ , 100000 )
INSERT INTO EMPLOYEE VALUES ( ‘Pawan’ , 100000 )
INSERT INTO EMPLOYEE VALUES ( ‘Gauri’ , 100000 )
INSERT INTO EMPLOYEE VALUES ( ‘Gauri’ , 100000 )
INSERT INTO EMPLOYEE VALUES ( ‘Rahul’ , 100000 )
INSERT INTO EMPLOYEE VALUES ( ‘Deepa’ , 100000 )

–Query to delete duplicate names in the above table
DELETE from EMPLOYEE where id NOT IN (SELECT MAX(id) FROM EMPLOYEE GROUP BY EmpName)

–Check with
SELECT * FROM EMPLOYEE

Note : If you do not have identity column then create one and remove that column after your deletion.