Please check the following code to find the duplicate data if you are using sql 2005 and update/delete data accordingly.
DECLARE @Duplicate TABLE (
ID INT,
FNAME VARCHAR(10),
MNAME VARCHAR(10)
)
INSERT INTO @Duplicate VALUES(1, ‘AAA’,'CCC’)
INSERT INTO @Duplicate VALUES(2, ‘BBB’,'DDD’)
INSERT INTO @Duplicate VALUES(1, ‘AAA’,'CCC’)
INSERT INTO @Duplicate VALUES(2, ‘BBB’,'DDD’)
INSERT INTO @Duplicate VALUES(1, ‘AAA’,'CCC’)
INSERT INTO @Duplicate VALUES(2, ‘BBB’,'DDD’)
INSERT INTO @Duplicate VALUES(3, ‘BCB’,'DGD’)
--FOR SQL SERVER 2005 and above
;WITH CTE as(
SELECT ROW_NUMBER() OVER(PARTITION BY ID, FName, MName ORDER BY (SELECT 1)) AS RowID,
*
FROM @Duplicate
)
SELECT ID, FName, MName
FROM CTE
WHERE RowID = 1
Reference:
http://blog.sqlauthority.com/2007/03/01/sql-server-delete-duplicate-records-rows/[
^]