Removal and delete of duplicate records in my table

Question

I have this table:

id  |  Name  | Age
==================
1   | AAA    | 22
1   | AAA    | 22
2   | BBB    | 33
2   | BBB    | 33
2   | BBB    | 33
3   | CCC    | 44
4   | DDD    | 55

I need to delete from this table all the duplicate records and leave only one record.

The table will looks like this:

id  |  Name  | Age
==================
1   | AAA    | 22
2   | BBB    | 33
3   | CCC    | 44
4   | DDD    | 55

I work with SQL Server CE for Mobile

Thanks

Gordon Linoff · Answer 1 · 2015-11-15T13:44:12.910

An easy way in SQL Server is to use an updatable CTE:

with todelete as (
      select t.*,
             row_number(*) over (partition by id, name, age) as seqnum
      from t
    )
delete todelete where seqnum > 1;

Alas, that doesn't work in SQL Server CE. Because your rows have no unique identifier, I think you need to create a temporary table and use a method like this:

create table temp_t (
    id int,
    name varchar(255),
    age int
   );

insert into temp_t select distinct * from t;

delete t;

insert into t select * from temp_t;

Another method uses an extra column instead of an extra table:

alter table t add tokeep int;

insert into t(id, name, age, tokeep)
    select distinct id, name, age, 1
    from t;

delete from t where tokeep is null;

alter table drop column tokeep;

Removal and delete of duplicate records in my table

1 Answers1