DELETE A DUPLICATE RECORD IN TABLE USING RANK AND WITH…
I had previously penned down two popular snippets regarding deleting duplicate rows and counting duplicate rows. 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 DUPLICATE (Col1 INT, Col2 INT)
INSERT INTO DUPLICATE
SELECT 1,1
UNION ALL
SELECT 1,1
UNION ALL
SELECT 1,1
UNION ALL
SELECT 1,2
UNION ALL
SELECT 1,2
UNION ALL
SELECT 1,3
UNION ALL
SELECT 1,4
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.
SELECT * FROM DUPLICATE
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 TEN (COL1,COL2.DUPL )
AS (SELECT COL1,COL2,
ROW_NOUMBER() OVER (PARTITION BY COL1,COL2 ODER BY COL1) AS DUPL
FROM DUPLICATE
)
DELETE
FROM TEN WHERE DUPL>1
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.
SELECT * FROM DUPLICATE
--
--
