Clean Records - Delete

[solomon_13000]

There are 5 columns in my table structure and they are:

1) Id - PK
2) Name
3) IdentityNo1
4) IdentityNo2
5) PassportNo

Since duplicate records are inserted into the table I am

attempting to remove the duplicate records. What makes

the records unique is the IdentityNo1, IdentityNo2 and

Passport column. However since there are duplicates so

the records are not unique. For example:

1, John, E6788, A9383 , null
2, James, null, null, E8364
3, John, E6788, A9383 , null
4, John, E6788, A9383 , null
5, James, null, null, E8364

The example above shows the record with Id (1, 3 and 4)

and (2, 5) have the same IdentityNo1. IdentityNo2 and

PassportNo. So now I intend to remove record 1, 3 and

keep record 4. I intend to remove record 2 and keep

record 5. Why record 4 and 5? It's because the records

are the last records and considered to be the latest.

Is there a way to implement this concept.

[pritaeas]

http://www.daniweb.com/web-development/databases/ms-sql/threads/70383/how-to-delete-duplicate-record-in-a-table-by-using-sql-query

[buddylee17]

There are several ways to do this. Fortunately you at least have a primary key.

Be smart and backup the database before attempting.

Here's an example solution using a CTE:

Create the test tables and populate:

CREATE TABLE PassportTest(
     Id int not null
    ,Name varchar(25) not null
    ,IdentityNo1 char(5) null
    ,IdentityNo2 char(5) null
    ,PassportNo char(5) null
)

INSERT PassportTest (Id, Name, IdentityNo1, IdentityNo2, PassportNo)
SELECT 1, 'John', 'E6788', 'A9383' , null
UNION ALL
SELECT 2, 'James', null, null, 'E8364'
UNION ALL
SELECT 3, 'John', 'E6788', 'A9383' , null
UNION ALL
SELECT 4, 'John', 'E6788', 'A9383' , null
UNION ALL
SELECT 5, 'James', null, null, 'E8364';

Now delete the dupes:

;WITH Keepers as (
    SELECT MAX(Id) Id
        ,Name
        ,IdentityNo1
        ,IdentityNo2
        ,PassportNo
    FROM PassportTest
    GROUP BY Name
        ,IdentityNo1
        ,IdentityNo2
        ,PassportNo
)
DELETE FROM PassportTest
WHERE Id NOT IN (
    SELECT Id FROM Keepers
);