Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2002
    Location
    London
    Posts
    8

    Question Unanswered: Duplicate Records

    HI all,

    Would some please be able to help me out here, I have a table in which I need to select all duplicate records and then eventually delete them [Sybase dB]

    Can someone pls help me out here with the syntax for the select and then the delete.

    The Table has no Primary key @ the moment, as soon as I've cleansed that data i'll assign them.

    Many thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select col1, col2, col3
    from yourtable
    group by col1, col2, col3
    having count(*) > 1

    here col1, col2, col3 are the columns which you want to screen for duplicate combinations

    since you want to eventually delete rows, could you please clarify -- delete all duplicates, or delete all but one of each?

    and if all but one, how will you determine which one? i.e. what other column will be used for this?


    rudy

  3. #3
    Join Date
    Sep 2002
    Location
    London
    Posts
    8

    Thanks Rudy

    Thanks Rudy......

    since you want to eventually delete rows, could you please clarify -- delete all duplicates, or delete all but one of each?
    >>>>> I would want to delete all duplicates...only keep on instance of the record

    and if all but one, how will you determine which one? i.e. what other column will be used for this?
    >>>>>I don't have an Index column, so I'm I'm not too sure how this can be done....
    I was thinking of Creating a new sybase table and assign my primary/indez keys and then try to import the data from the original tanle into the new one, all duplicated inserts that violate the primary key would then be aborted...?

    But not to sure how to import that data once I've created the structure of the table...

    What do you think???

    Thanks,
    Rocks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry for the delay replying

    if you have a primary key in mind, perhaps you could give a few examples of so-called duplicated rows and which one of the group you might want to keep

    rudy

  5. #5
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697

    Re: Thanks Rudy

    I was thinking of Creating a new sybase table and assign my primary/indez keys and then try to import the data from the original tanle into the new one, all duplicated inserts that violate the primary key would then be aborted...?
    If you only have one non-key value:

    Select a.key1, a.key2, ..., a.val
    FROM table a
    WHERE a.val IN (SELECT TOP 1 val from table b
    WHERE a.key1 = b.key1 and a.key2 = b.key2 and...)

    Then you realize that it's nigh-impossible to compare rows in SQL and you wind up doing something with cursors.

  6. #6
    Join Date
    Sep 2002
    Location
    London
    Posts
    8

    Talking Thanks guys!!!!

    Managed to sort this out!

    Thanks for the help!!!

    Rocks

Posting Permissions

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