Delete Duplicate Rows in SQL Server 2005 and 2008 using CTE

First we will create a table and insert duplicate rows from which we will later remove duplicate rows:

CREATE TABLE Duplicate_Record_Table (Value1 char, Value2 char)
INSERT INTO Duplicate_Record_Table (Value1, Value2)
VALUES ('B','A');
INSERT INTO Duplicate_Record_Table (Value1, Value2)
VALUES ('A','A');
INSERT INTO Duplicate_Record_Table (Value1, Value2)
VALUES ('B','A');
INSERT INTO Duplicate_Record_Table (Value1, Value2)
VALUES ('A','A');
INSERT INTO Duplicate_Record_Table (Value1, Value2)
VALUES ('B','B');
INSERT INTO Duplicate_Record_Table (Value1, Value2)
VALUES ('B','A');
INSERT INTO Duplicate_Record_Table (Value1, Value2)
VALUES ('B','B');
INSERT INTO Duplicate_Record_Table (Value1, Value2)
VALUES ('A','B');
INSERT INTO Duplicate_Record_Table (Value1, Value2)
VALUES ('A','B');
INSERT INTO Duplicate_Record_Table (Value1, Value2)
VALUES ('A','B');
INSERT INTO Duplicate_Record_Table (Value1, Value2)
VALUES ('A','B');
INSERT INTO Duplicate_Record_Table (Value1, Value2)
VALUES ('A','B');
GO

We can see that there are more duplicate rows in the table:

SELECT * FROM Duplicate_Record_Table

To delete duplicate rows run following code (in this example a duplicate row in the table with two columns is when both Value1 and Value2 are the same):

WITH CTE (Value1,Value2, Duplicate_Record_Count)
AS
(
SELECT Value1,Value2,
ROW_NUMBER() OVER(PARTITION BY Value1,Value2 ORDER BY Value1) AS Duplicate_Record_Count
FROM Duplicate_Record_Table
)
DELETE
FROM CTE
WHERE Duplicate_Record_Count > 1
GO

...and 8 duplicate rows are deleted from the table:

To see how to count duplicate records or rows in SQL Server read article:

Count duplicate records or rows in SQL Server

If the post helped you, please share it:
Pin It

2 comments to Delete Duplicate Rows in SQL Server 2005 and 2008 using CTE

  • dinesh

    while creating show CTE it show error message.

    Msg 102, Level 15, State 1, Line 8
    Incorrect syntax near ')'.

    Please give fast solution .it is urgent.

    • Paul

      If you created the table as shown, then the CTE in the example:

      WITH CTE (Value1,Value2, Duplicate_Record_Count)
      AS
      (
      SELECT Value1,Value2,
      ROW_NUMBER() OVER(PARTITION BY Value1,Value2 ORDER BY Value1) AS Duplicate_Record_Count
      FROM Duplicate_Record_Table
      )
      DELETE
      FROM CTE
      WHERE Duplicate_Record_Count > 1
      GO

      ...should execute without errors.
      Did you use the CTE from the example?

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">