Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Posts
    3

    Red face Unanswered: Please Help: Help removing all but the FIRST of duplicate records

    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, [B]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. #2
    Join Date
    Aug 2009
    Posts
    3
    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. #3
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    Code:
    create temp table tp01 (f1 smallint, f2 smallint, desc char(5));
    Temporary table created.                                        
    
    insert into tp01 values(20, 1090, "bad");
    1 row(s) inserted.                       
    insert into tp01 values(22, 1090, "bad");
    1 row(s) inserted.                       
    insert into tp01 values(25, 1090, "bad");
    1 row(s) inserted.                       
    insert into tp01 values(28, 1091, "good");
    1 row(s) inserted.                        
    insert into tp01 values(2350, 2444, "bad");
    1 row(s) inserted.                         
    insert into tp01 values(2353, 2444, "bad");
    1 row(s) inserted.                         
    insert into tp01 values(2370, 2444, "bad");
    1 row(s) inserted.                         
    insert into tp01 values(3000, 5000, "good");
    1 row(s) inserted.                          
    insert into tp01 values(3005, 5001, "good");
    1 row(s) inserted.
    
    select f2,max(rowid) rowid_ok from tp01
    group by 1
    into temp tp02;
    5 row(s) retrieved into temp table.
    
    delete from tp01
    where rowid not in ( select rowid_ok from tp02);
    4 row(s) deleted.
    
    select * from tp01;
    
        f1     f2 desc
    
        25   1090 bad
        28   1091 good
      2370   2444 bad
      3000   5000 good
      3005   5001 good
    5 row(s) retrieved.
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    how about select distinct col1,col2,col3 from your_table and then load that data?
    Dave

Posting Permissions

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