0

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.

Edited by solomon_13000

3
Contributors
2
Replies
4
Views
4 Years
Discussion Span
Last Post by buddylee17
0

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
);

Edited by buddylee17

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.