Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    12

    Unanswered: Delete older entries with duplicate names

    Assume I have the following table.

    id name
    -- ----------
    1 John
    2 Josh
    3 Mike
    4 John
    5 Dana
    6 Josh
    7 John

    I want to delete the older entries of the duplicate names. So in this instance, I want to delete id 1, 2 and 4.

    Thanks ahead of time...

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Delete older entries with duplicate names

    drop table table1
    go
    create table table1(id int
    ,iname varchar(10))
    go
    insert table1 select 1,'John'
    insert table1 select 2,'John'
    insert table1 select 3,'Mike'
    insert table1 select 4,'John'
    insert table1 select 5,'Dana'
    insert table1 select 6,'Josh'
    insert table1 select 7,'John'
    go
    select *
    --delete
    from table1
    where iname in (select iname from table1 group by iname having count(*)>1)
    and id not in (select max(id) from table1 group by iname having count(*)>1)

  3. #3
    Join Date
    Jan 2003
    Posts
    12
    Is there not a way to do it programmatically without defining which names to re-insert? I have a few hundred rows of duplicates

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by jiggle it
    Is there not a way to do it programmatically without defining which names to re-insert? I have a few hundred rows of duplicates
    What do you mean "to re-insert"? Just run last query and all older reconds for duplicates will be gone.

  5. #5
    Join Date
    Jan 2003
    Posts
    12

  6. #6
    Join Date
    May 2003
    Posts
    26
    DELETE Table1
    WHERE id IN
    (SELECT A.id from Table1 A, Table1 B
    WHERE A.name = B.name
    AND A.id < B.id)

    May not be as efficient but requires less typing, which is a plus in my book )
    Last edited by Donner; 09-17-03 at 13:43.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •