Loading ...

SQL: Delete duplicate rows from a table where there is no primary key i | CodeAsp.Net

SQL: Delete duplicate rows from a table when there is no primary key in the table

 /5
0 (0votes)

In this blog I will help you how to delete duplicate rows from a table where there is no primary key in the table. Below is the script to insert data:

CREATE TABLE MyTable
    (
      Col1 INT ,
      Col2 VARCHAR(10) ,
      Col3 VARCHAR(11)
    );

GO
INSERT  INTO MyTable
VALUES  ( 1, 'Jack', '555-55-5555' );
INSERT  INTO MyTable
VALUES  ( 2, 'Joe', '555-56-5555' );
INSERT  INTO MyTable
VALUES  ( 3, 'Fred', '555-57-5555' );
INSERT  INTO MyTable
VALUES  ( 4, 'Mike', '555-58-5555' );
INSERT  INTO MyTable
VALUES  ( 5, 'Cathy', '555-59-5555' );
INSERT  INTO MyTable
VALUES  ( 6, 'Lisa', '555-70-5555' );
INSERT  INTO MyTable
VALUES  ( 1, 'Jack', '555-55-5555' );
INSERT  INTO MyTable
VALUES  ( 4, 'Mike', '555-58-5555' );
INSERT  INTO MyTable
VALUES  ( 5, 'Cathy', '555-59-5555' );
INSERT  INTO MyTable
VALUES  ( 6, 'Lisa', '555-70-5555' );
INSERT  INTO MyTable
VALUES  ( 5, 'Cathy', '555-59-5555' );
INSERT  INTO MyTable
VALUES  ( 6, 'Lisa', '555-70-5555' );

GO

You will notice the duplicate rows above:

Solution:

The duplicate rows can be removed like this:

DELETE  SUB
FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY Col1,
                                        Col2, Col3 ORDER BY Col1 ) cnt
          FROM      MyTable
        ) SUB
WHERE   SUB.Cnt > 1

After running the above query duplicate rows will be removed:

Comments (no comments yet)

Top Posts