1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

please Help: Help removing all but the FIRST of duplicate records

Discussion in 'Database' started by wgre0111, Aug 19, 2009.

  1. wgre0111

    wgre0111 New Member

    Joined:
    Oct 19, 2008
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    0
    Greetings,

    I am trying to remove duplicate records from a table but preserving one of them using temp tables/etc. Pretty much removing duplicates but leaving one of the identical sets.

    Example DATA would like like:

    col1, col2, col3
    20, 1090, bad
    22, 1090, bad
    25, 1090, bad
    28, 1091, good
    2350, 2444, bad
    2353, 2444, bad
    2370, 2444, bad
    3000, 5000, good
    3005, 5001, good



    AFTER the DELETE sql (which I need help with)
    example data (based off of above) should look like


    col1, col2[/ col3
    20, 1090, bad
    28, 1091, good
    2350, 2444, bad
    3000, 5000, good
    3005, 50001, good


    Please notice that one of each of the sets that had duplicates remains.



    The records the new sql would need to focus on would be records on col2 With counts(col2) >1 should not have two col2's with data like 1090. All but one of them would need to be deleted. Doesnt matter which one is left.


    Please help!
     
  2. wgre0111

    wgre0111 New Member

    Joined:
    Oct 19, 2008
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    0
    Something I would like to try is building a temp table and just comparing original table to the temp table... Dont know how to do this in informix though.... Here is a sketch.

    delete Table1
    from origtable Table1, temp_table Table2
    where Table1.col2 = Table2.col2
    and Table1.col1 > table2.col2
     
  3. xpi0t0s

    xpi0t0s Mentor

    Joined:
    Aug 6, 2004
    Messages:
    3,012
    Likes Received:
    203
    Trophy Points:
    0
    Occupation:
    Senior Support Engineer
    Location:
    England
    Don't know if you can do it in a single query but if you SELECT DISTINCT col2 in a cursor, then loop over that cursor you can SELECT MIN(col1) ... WHERE col2=<cursor value>.
     
  4. naimish

    naimish New Member

    Joined:
    Jun 29, 2009
    Messages:
    1,046
    Likes Received:
    18
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    On Earth
    Hope he is using SQL ;) lol
     
  5. xpi0t0s

    xpi0t0s Mentor

    Joined:
    Aug 6, 2004
    Messages:
    3,012
    Likes Received:
    203
    Trophy Points:
    0
    Occupation:
    Senior Support Engineer
    Location:
    England
    well he referred to SQL in both posts so I would say that's a yes.
     
  6. nimesh

    nimesh New Member

    Joined:
    Apr 13, 2009
    Messages:
    770
    Likes Received:
    20
    Trophy Points:
    0
    Occupation:
    Oracle Apps Admin
    Location:
    Mumbai
    Home Page:
    I think he's referring to generic sql, and the tool he referred is informix.
    Does informix has it's own sql language, or it uses T-SQL or PL/SQL
     

Share This Page