Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table in example is has ID as Identity Column and Columns which have duplicate data are DuplicateColumn1, DuplicateColumn2 and DuplicateColumn3.
DELETE
FROM MyTableWHERE ID NOT IN(SELECT MAX(ID)FROM MyTableGROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)
or
DELETE
FROM test1
WHERE ide NOT IN
(
SELECT MAX(ide)
FROM test1
GROUP BY num, user_name, active)
or
DELETE
FROM test1
WHERE test1.%%physloc%%
NOT IN (SELECT MIN(b.%%physloc%%)
FROM test1 b
GROUP BY b.num, b.user_name,b.active)
DELETE
FROM test1
WHERE ide NOT IN
(
SELECT MAX(ide)
FROM test1
GROUP BY num, user_name, active)
or
DELETE
FROM test1
WHERE test1.%%physloc%%
NOT IN (SELECT MIN(b.%%physloc%%)
FROM test1 b
GROUP BY b.num, b.user_name,b.active)
Today, we will examine another very quick code snippet where we will delete duplicate rows using CTE and ROW_NUMBER() feature of SQL Server 2005 and SQL Server 2008.
This method is improved over the earlier method as it not only uses CTE and ROW_NUMBER, but also demonstrates the power of CTE with DELETE statement. We will have a comprehensive discussion about it later in this article. For now, let us first create a sample table from which we will delete records.
/* Create Table with 7 entries - 3 are duplicate entries */CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)INSERT INTO DuplicateRcordTableSELECT 1, 1UNION ALLSELECT 1, 1 --duplicateUNION ALLSELECT 1, 1 --duplicateUNION ALLSELECT 1, 2UNION ALLSELECT 1, 2 --duplicateUNION ALLSELECT 1, 3UNION ALLSELECT 1, 4
GO
The above table has total 7 records, out of which 3 are duplicate records. Once the duplicates are removed we will have only 4 records left.
/* It should give you 7 rows */SELECT *FROM DuplicateRcordTable
GO
The most interesting part of this is yet to come. We will use CTE that will re-generate the same table with additional column, which is row number. In our case, we have Col1 and Col2 and both the columns qualify as duplicate rows. It may be a different set of rows for each different query like this. Another point to note here is that once CTE is created DELETE statement can be run on it. We will put a condition here – when we receive more than one rows of record, we will remove the row which is not the first one. When DELETE command is executed over CTE it in fact deletes from the base table used in CTE.
/* Delete Duplicate records */WITH CTE (COl1,Col2, DuplicateCount)AS(SELECT COl1,Col2,ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) ASDuplicateCountFROM DuplicateRcordTable)DELETE
FROM CTEWHERE DuplicateCount > 1
GO
It is apparent that after delete command has been run, we will have only 4 records, which is almost the same result which we would have got with DISTINCT, with this resultset. If we had more than 2 columns and we had to run unique on only two columns, our distinct might have not worked here . In this case, we would have to use above the mentioned method.
/* It should give you Distinct 4 records */SELECT *FROM DuplicateRcordTable
GO