Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2007
    Posts
    28

    Unanswered: help on find duplicates and delete procedure

    Hi everybody I need help on finding duplicates and deleting the duplicate record depending on name and fname , deleting the duplicates and leaving only the first one.

    my PERSON table is this below:

    ID name fname ownerid id2

    1 a b
    2 c c
    3 e f
    4 a b 1 10
    5 c c 2 11

    I have this query below that returns records 1 and 4 and 2 and 5 since they have the same name and fname

    select * from ( Select name ,fname, count(1) as cnt from PERSON group by
    name,Fname ) where cnt > 1


    ID name fname ownerid id2

    1 a b
    4 a b 1 10

    2 c c
    5 c c 2 11


    With this result I need to delete the second record of each group but update the first records with the ownerid and id2 of the second record that would be deleted... I don't know how to proceed with this..

    thanks
    alex

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how do you know which one is the "second" record?

    and why go to the trouble of updating the one you keep? why not delete the one that doesn't have values in ownerid and id2?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2007
    Posts
    28
    Quote Originally Posted by r937
    how do you know which one is the "second" record?

    and why go to the trouble of updating the one you keep? why not delete the one that doesn't have values in ownerid and id2?
    the second record is the one with ownerid and id2 well the reason behind is that the second record is inserted from another table and the id of the first one is the one i need due to relationships with other tables

  4. #4
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    I see trouble ahead If you update the missing data in the "first" of the duplicate rows, then how are you going to figure out which row is which when you later attempt to delete the "second" row?

    Keep in mind that your question/description is rife with suggestions of "order" in your data. Beware "order" where there is none but that which is artificially created. (I need to get a job writing fortune cookies).

    Probably you will need to add a column or work in a temporary table so that you can artificially add a "flag" of some type so you know which rows are the "good" rows and which ones you will later slaughter mercilessly after brazenly and carelessly using them only for their data.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  5. #5
    Join Date
    Jun 2007
    Posts
    28
    Quote Originally Posted by TallCowboy0614
    I see trouble ahead If you update the missing data in the "first" of the duplicate rows, then how are you going to figure out which row is which when you later attempt to delete the "second" row?

    Keep in mind that your question/description is rife with suggestions of "order" in your data. Beware "order" where there is none but that which is artificially created. (I need to get a job writing fortune cookies).

    Probably you will need to add a column or work in a temporary table so that you can artificially add a "flag" of some type so you know which rows are the "good" rows and which ones you will later slaughter mercilessly after brazenly and carelessly using them only for their data.

    yes thanks for your suggestion will try it then...

  6. #6
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    this might work for u ....
    [code]
    delete from Table1 where ID in
    (select ID from Table1, (Select name ,fname, count(*) as cnt from Table1 group by name,Fname having count(*) > 1) as xx
    where Table1.name=xx.name and Table1.fname=xx.fname and Table1.ownerid is not null and Table1.id2 is not null)
    [code]

Posting Permissions

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