SQL Server - how to delete duplicate rows from table

Asked By Anto Bilson on 15-Mar-12 03:54 AM


 i've table called lanuage contains more than one duplicate entries in table ..how to delete duplicate values from table

pls suggest me
dipa ahuja replied to Anto Bilson on 15-Mar-12 03:56 AM
DELETE FROM table_a
WHERE column_a = 'XXX' AND column_b = 'YYY' and column_c = 'ZZZ'
AND rownum < 2

More :

http://blog.sqlauthority.com/2007/03/01/sql-server-delete-duplicate-records-rows/ 
Somesh Yadav replied to Anto Bilson on 15-Mar-12 04:31 AM

First, insert an identity column in that table by using the following code:

ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1)  

Now the table data will be like the following table:

EMPLOYEE_ID ATTENDANCE_DATE AUTOID
A001 2011-01-01 1
A001 2011-01-01 2
A002 2011-01-01 3
A002 2011-01-01 4
A002 2011-01-01 5
A003 2011-01-01 6

Check the AUTOID column. Now we will start playing the game with this column.

Now use the following code to find out the duplicate rows that exist in the table.

SELECT * FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
	FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)  

The above code will give us the following result:

EMPLOYEE_ID ATTENDANCE_DATE AUTOID
A001 2011-01-01 2
A002 2011-01-01 4
A002 2011-01-01 5

Ultimately, these are the duplicate rows which we want to delete to resolve the issue. Use the following code to resolve it.

DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
	FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE) 

Now check the data. No duplicate rows exist in the table.